codekingpro/portable-devtools
114k
1# dialects/oracle/dictionary.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9from .types import DATE
10from .types import LONG
11from .types import NUMBER
12from .types import RAW
13from .types import VARCHAR2
14from ... import Column
15from ... import MetaData
16from ... import Table
17from ... import table
18from ...sql.sqltypes import CHAR
19
20# constants
21DB_LINK_PLACEHOLDER = "__$sa_dblink$__"
22# tables
23dual = table("dual")
24dictionary_meta = MetaData()
25
26# NOTE: all the dictionary_meta are aliases because oracle does not like
27# using the full table@dblink for every column in query, and complains with
28# ORA-00960: ambiguous column naming in select list
29all_tables = Table(
30 "all_tables" + DB_LINK_PLACEHOLDER,
31 dictionary_meta,
32 Column("owner", VARCHAR2(128), nullable=False),
33 Column("table_name", VARCHAR2(128), nullable=False),
34 Column("tablespace_name", VARCHAR2(30)),
35 Column("cluster_name", VARCHAR2(128)),
36 Column("iot_name", VARCHAR2(128)),
37 Column("status", VARCHAR2(8)),
38 Column("pct_free", NUMBER),
39 Column("pct_used", NUMBER),
40 Column("ini_trans", NUMBER),
41 Column("max_trans", NUMBER),
42 Column("initial_extent", NUMBER),
43 Column("next_extent", NUMBER),
44 Column("min_extents", NUMBER),
45 Column("max_extents", NUMBER),
46 Column("pct_increase", NUMBER),
47 Column("freelists", NUMBER),
48 Column("freelist_groups", NUMBER),
49 Column("logging", VARCHAR2(3)),
50 Column("backed_up", VARCHAR2(1)),
51 Column("num_rows", NUMBER),
52 Column("blocks", NUMBER),
53 Column("empty_blocks", NUMBER),
54 Column("avg_space", NUMBER),
55 Column("chain_cnt", NUMBER),
56 Column("avg_row_len", NUMBER),
57 Column("avg_space_freelist_blocks", NUMBER),
58 Column("num_freelist_blocks", NUMBER),
59 Column("degree", VARCHAR2(10)),
60 Column("instances", VARCHAR2(10)),
61 Column("cache", VARCHAR2(5)),
62 Column("table_lock", VARCHAR2(8)),
63 Column("sample_size", NUMBER),
64 Column("last_analyzed", DATE),
65 Column("partitioned", VARCHAR2(3)),
66 Column("iot_type", VARCHAR2(12)),
67 Column("temporary", VARCHAR2(1)),
68 Column("secondary", VARCHAR2(1)),
69 Column("nested", VARCHAR2(3)),
70 Column("buffer_pool", VARCHAR2(7)),
71 Column("flash_cache", VARCHAR2(7)),
72 Column("cell_flash_cache", VARCHAR2(7)),
73 Column("row_movement", VARCHAR2(8)),
74 Column("global_stats", VARCHAR2(3)),
75 Column("user_stats", VARCHAR2(3)),
76 Column("duration", VARCHAR2(15)),
77 Column("skip_corrupt", VARCHAR2(8)),
78 Column("monitoring", VARCHAR2(3)),
79 Column("cluster_owner", VARCHAR2(128)),
80 Column("dependencies", VARCHAR2(8)),
81 Column("compression", VARCHAR2(8)),
82 Column("compress_for", VARCHAR2(30)),
83 Column("dropped", VARCHAR2(3)),
84 Column("read_only", VARCHAR2(3)),
85 Column("segment_created", VARCHAR2(3)),
86 Column("result_cache", VARCHAR2(7)),
87 Column("clustering", VARCHAR2(3)),
88 Column("activity_tracking", VARCHAR2(23)),
89 Column("dml_timestamp", VARCHAR2(25)),
90 Column("has_identity", VARCHAR2(3)),
91 Column("container_data", VARCHAR2(3)),
92 Column("inmemory", VARCHAR2(8)),
93 Column("inmemory_priority", VARCHAR2(8)),
94 Column("inmemory_distribute", VARCHAR2(15)),
95 Column("inmemory_compression", VARCHAR2(17)),
96 Column("inmemory_duplicate", VARCHAR2(13)),
97 Column("default_collation", VARCHAR2(100)),
98 Column("duplicated", VARCHAR2(1)),
99 Column("sharded", VARCHAR2(1)),
100 Column("externally_sharded", VARCHAR2(1)),
101 Column("externally_duplicated", VARCHAR2(1)),
102 Column("external", VARCHAR2(3)),
103 Column("hybrid", VARCHAR2(3)),
104 Column("cellmemory", VARCHAR2(24)),
105 Column("containers_default", VARCHAR2(3)),
106 Column("container_map", VARCHAR2(3)),
107 Column("extended_data_link", VARCHAR2(3)),
108 Column("extended_data_link_map", VARCHAR2(3)),
109 Column("inmemory_service", VARCHAR2(12)),
110 Column("inmemory_service_name", VARCHAR2(1000)),
111 Column("container_map_object", VARCHAR2(3)),
112 Column("memoptimize_read", VARCHAR2(8)),
113 Column("memoptimize_write", VARCHAR2(8)),
114 Column("has_sensitive_column", VARCHAR2(3)),
115 Column("admit_null", VARCHAR2(3)),
116 Column("data_link_dml_enabled", VARCHAR2(3)),
117 Column("logical_replication", VARCHAR2(8)),
118).alias("a_tables")
119
120all_views = Table(
121 "all_views" + DB_LINK_PLACEHOLDER,
122 dictionary_meta,
123 Column("owner", VARCHAR2(128), nullable=False),
124 Column("view_name", VARCHAR2(128), nullable=False),
125 Column("text_length", NUMBER),
126 Column("text", LONG),
127 Column("text_vc", VARCHAR2(4000)),
128 Column("type_text_length", NUMBER),
129 Column("type_text", VARCHAR2(4000)),
130 Column("oid_text_length", NUMBER),
131 Column("oid_text", VARCHAR2(4000)),
132 Column("view_type_owner", VARCHAR2(128)),
133 Column("view_type", VARCHAR2(128)),
134 Column("superview_name", VARCHAR2(128)),
135 Column("editioning_view", VARCHAR2(1)),
136 Column("read_only", VARCHAR2(1)),
137 Column("container_data", VARCHAR2(1)),
138 Column("bequeath", VARCHAR2(12)),
139 Column("origin_con_id", VARCHAR2(256)),
140 Column("default_collation", VARCHAR2(100)),
141 Column("containers_default", VARCHAR2(3)),
142 Column("container_map", VARCHAR2(3)),
143 Column("extended_data_link", VARCHAR2(3)),
144 Column("extended_data_link_map", VARCHAR2(3)),
145 Column("has_sensitive_column", VARCHAR2(3)),
146 Column("admit_null", VARCHAR2(3)),
147 Column("pdb_local_only", VARCHAR2(3)),
148).alias("a_views")
149
150all_sequences = Table(
151 "all_sequences" + DB_LINK_PLACEHOLDER,
152 dictionary_meta,
153 Column("sequence_owner", VARCHAR2(128), nullable=False),
154 Column("sequence_name", VARCHAR2(128), nullable=False),
155 Column("min_value", NUMBER),
156 Column("max_value", NUMBER),
157 Column("increment_by", NUMBER, nullable=False),
158 Column("cycle_flag", VARCHAR2(1)),
159 Column("order_flag", VARCHAR2(1)),
160 Column("cache_size", NUMBER, nullable=False),
161 Column("last_number", NUMBER, nullable=False),
162 Column("scale_flag", VARCHAR2(1)),
163 Column("extend_flag", VARCHAR2(1)),
164 Column("sharded_flag", VARCHAR2(1)),
165 Column("session_flag", VARCHAR2(1)),
166 Column("keep_value", VARCHAR2(1)),
167).alias("a_sequences")
168
169all_users = Table(
170 "all_users" + DB_LINK_PLACEHOLDER,
171 dictionary_meta,
172 Column("username", VARCHAR2(128), nullable=False),
173 Column("user_id", NUMBER, nullable=False),
174 Column("created", DATE, nullable=False),
175 Column("common", VARCHAR2(3)),
176 Column("oracle_maintained", VARCHAR2(1)),
177 Column("inherited", VARCHAR2(3)),
178 Column("default_collation", VARCHAR2(100)),
179 Column("implicit", VARCHAR2(3)),
180 Column("all_shard", VARCHAR2(3)),
181 Column("external_shard", VARCHAR2(3)),
182).alias("a_users")
183
184all_mviews = Table(
185 "all_mviews" + DB_LINK_PLACEHOLDER,
186 dictionary_meta,
187 Column("owner", VARCHAR2(128), nullable=False),
188 Column("mview_name", VARCHAR2(128), nullable=False),
189 Column("container_name", VARCHAR2(128), nullable=False),
190 Column("query", LONG),
191 Column("query_len", NUMBER(38)),
192 Column("updatable", VARCHAR2(1)),
193 Column("update_log", VARCHAR2(128)),
194 Column("master_rollback_seg", VARCHAR2(128)),
195 Column("master_link", VARCHAR2(128)),
196 Column("rewrite_enabled", VARCHAR2(1)),
197 Column("rewrite_capability", VARCHAR2(9)),
198 Column("refresh_mode", VARCHAR2(6)),
199 Column("refresh_method", VARCHAR2(8)),
200 Column("build_mode", VARCHAR2(9)),
201 Column("fast_refreshable", VARCHAR2(18)),
202 Column("last_refresh_type", VARCHAR2(8)),
203 Column("last_refresh_date", DATE),
204 Column("last_refresh_end_time", DATE),
205 Column("staleness", VARCHAR2(19)),
206 Column("after_fast_refresh", VARCHAR2(19)),
207 Column("unknown_prebuilt", VARCHAR2(1)),
208 Column("unknown_plsql_func", VARCHAR2(1)),
209 Column("unknown_external_table", VARCHAR2(1)),
210 Column("unknown_consider_fresh", VARCHAR2(1)),
211 Column("unknown_import", VARCHAR2(1)),
212 Column("unknown_trusted_fd", VARCHAR2(1)),
213 Column("compile_state", VARCHAR2(19)),
214 Column("use_no_index", VARCHAR2(1)),
215 Column("stale_since", DATE),
216 Column("num_pct_tables", NUMBER),
217 Column("num_fresh_pct_regions", NUMBER),
218 Column("num_stale_pct_regions", NUMBER),
219 Column("segment_created", VARCHAR2(3)),
220 Column("evaluation_edition", VARCHAR2(128)),
221 Column("unusable_before", VARCHAR2(128)),
222 Column("unusable_beginning", VARCHAR2(128)),
223 Column("default_collation", VARCHAR2(100)),
224 Column("on_query_computation", VARCHAR2(1)),
225 Column("auto", VARCHAR2(3)),
226).alias("a_mviews")
227
228all_tab_identity_cols = Table(
229 "all_tab_identity_cols" + DB_LINK_PLACEHOLDER,
230 dictionary_meta,
231 Column("owner", VARCHAR2(128), nullable=False),
232 Column("table_name", VARCHAR2(128), nullable=False),
233 Column("column_name", VARCHAR2(128), nullable=False),
234 Column("generation_type", VARCHAR2(10)),
235 Column("sequence_name", VARCHAR2(128), nullable=False),
236 Column("identity_options", VARCHAR2(298)),
237).alias("a_tab_identity_cols")
238
239all_tab_cols = Table(
240 "all_tab_cols" + DB_LINK_PLACEHOLDER,
241 dictionary_meta,
242 Column("owner", VARCHAR2(128), nullable=False),
243 Column("table_name", VARCHAR2(128), nullable=False),
244 Column("column_name", VARCHAR2(128), nullable=False),
245 Column("data_type", VARCHAR2(128)),
246 Column("data_type_mod", VARCHAR2(3)),
247 Column("data_type_owner", VARCHAR2(128)),
248 Column("data_length", NUMBER, nullable=False),
249 Column("data_precision", NUMBER),
250 Column("data_scale", NUMBER),
251 Column("nullable", VARCHAR2(1)),
252 Column("column_id", NUMBER),
253 Column("default_length", NUMBER),
254 Column("data_default", LONG),
255 Column("num_distinct", NUMBER),
256 Column("low_value", RAW(1000)),
257 Column("high_value", RAW(1000)),
258 Column("density", NUMBER),
259 Column("num_nulls", NUMBER),
260 Column("num_buckets", NUMBER),
261 Column("last_analyzed", DATE),
262 Column("sample_size", NUMBER),
263 Column("character_set_name", VARCHAR2(44)),
264 Column("char_col_decl_length", NUMBER),
265 Column("global_stats", VARCHAR2(3)),
266 Column("user_stats", VARCHAR2(3)),
267 Column("avg_col_len", NUMBER),
268 Column("char_length", NUMBER),
269 Column("char_used", VARCHAR2(1)),
270 Column("v80_fmt_image", VARCHAR2(3)),
271 Column("data_upgraded", VARCHAR2(3)),
272 Column("hidden_column", VARCHAR2(3)),
273 Column("virtual_column", VARCHAR2(3)),
274 Column("segment_column_id", NUMBER),
275 Column("internal_column_id", NUMBER, nullable=False),
276 Column("histogram", VARCHAR2(15)),
277 Column("qualified_col_name", VARCHAR2(4000)),
278 Column("user_generated", VARCHAR2(3)),
279 Column("default_on_null", VARCHAR2(3)),
280 Column("identity_column", VARCHAR2(3)),
281 Column("evaluation_edition", VARCHAR2(128)),
282 Column("unusable_before", VARCHAR2(128)),
283 Column("unusable_beginning", VARCHAR2(128)),
284 Column("collation", VARCHAR2(100)),
285 Column("collated_column_id", NUMBER),
286).alias("a_tab_cols")
287
288all_tab_comments = Table(
289 "all_tab_comments" + DB_LINK_PLACEHOLDER,
290 dictionary_meta,
291 Column("owner", VARCHAR2(128), nullable=False),
292 Column("table_name", VARCHAR2(128), nullable=False),
293 Column("table_type", VARCHAR2(11)),
294 Column("comments", VARCHAR2(4000)),
295 Column("origin_con_id", NUMBER),
296).alias("a_tab_comments")
297
298all_col_comments = Table(
299 "all_col_comments" + DB_LINK_PLACEHOLDER,
300 dictionary_meta,
301 Column("owner", VARCHAR2(128), nullable=False),
302 Column("table_name", VARCHAR2(128), nullable=False),
303 Column("column_name", VARCHAR2(128), nullable=False),
304 Column("comments", VARCHAR2(4000)),
305 Column("origin_con_id", NUMBER),
306).alias("a_col_comments")
307
308all_mview_comments = Table(
309 "all_mview_comments" + DB_LINK_PLACEHOLDER,
310 dictionary_meta,
311 Column("owner", VARCHAR2(128), nullable=False),
312 Column("mview_name", VARCHAR2(128), nullable=False),
313 Column("comments", VARCHAR2(4000)),
314).alias("a_mview_comments")
315
316all_ind_columns = Table(
317 "all_ind_columns" + DB_LINK_PLACEHOLDER,
318 dictionary_meta,
319 Column("index_owner", VARCHAR2(128), nullable=False),
320 Column("index_name", VARCHAR2(128), nullable=False),
321 Column("table_owner", VARCHAR2(128), nullable=False),
322 Column("table_name", VARCHAR2(128), nullable=False),
323 Column("column_name", VARCHAR2(4000)),
324 Column("column_position", NUMBER, nullable=False),
325 Column("column_length", NUMBER, nullable=False),
326 Column("char_length", NUMBER),
327 Column("descend", VARCHAR2(4)),
328 Column("collated_column_id", NUMBER),
329).alias("a_ind_columns")
330
331all_indexes = Table(
332 "all_indexes" + DB_LINK_PLACEHOLDER,
333 dictionary_meta,
334 Column("owner", VARCHAR2(128), nullable=False),
335 Column("index_name", VARCHAR2(128), nullable=False),
336 Column("index_type", VARCHAR2(27)),
337 Column("table_owner", VARCHAR2(128), nullable=False),
338 Column("table_name", VARCHAR2(128), nullable=False),
339 Column("table_type", CHAR(11)),
340 Column("uniqueness", VARCHAR2(9)),
341 Column("compression", VARCHAR2(13)),
342 Column("prefix_length", NUMBER),
343 Column("tablespace_name", VARCHAR2(30)),
344 Column("ini_trans", NUMBER),
345 Column("max_trans", NUMBER),
346 Column("initial_extent", NUMBER),
347 Column("next_extent", NUMBER),
348 Column("min_extents", NUMBER),
349 Column("max_extents", NUMBER),
350 Column("pct_increase", NUMBER),
351 Column("pct_threshold", NUMBER),
352 Column("include_column", NUMBER),
353 Column("freelists", NUMBER),
354 Column("freelist_groups", NUMBER),
355 Column("pct_free", NUMBER),
356 Column("logging", VARCHAR2(3)),
357 Column("blevel", NUMBER),
358 Column("leaf_blocks", NUMBER),
359 Column("distinct_keys", NUMBER),
360 Column("avg_leaf_blocks_per_key", NUMBER),
361 Column("avg_data_blocks_per_key", NUMBER),
362 Column("clustering_factor", NUMBER),
363 Column("status", VARCHAR2(8)),
364 Column("num_rows", NUMBER),
365 Column("sample_size", NUMBER),
366 Column("last_analyzed", DATE),
367 Column("degree", VARCHAR2(40)),
368 Column("instances", VARCHAR2(40)),
369 Column("partitioned", VARCHAR2(3)),
370 Column("temporary", VARCHAR2(1)),
371 Column("generated", VARCHAR2(1)),
372 Column("secondary", VARCHAR2(1)),
373 Column("buffer_pool", VARCHAR2(7)),
374 Column("flash_cache", VARCHAR2(7)),
375 Column("cell_flash_cache", VARCHAR2(7)),
376 Column("user_stats", VARCHAR2(3)),
377 Column("duration", VARCHAR2(15)),
378 Column("pct_direct_access", NUMBER),
379 Column("ityp_owner", VARCHAR2(128)),
380 Column("ityp_name", VARCHAR2(128)),
381 Column("parameters", VARCHAR2(1000)),
382 Column("global_stats", VARCHAR2(3)),
383 Column("domidx_status", VARCHAR2(12)),
384 Column("domidx_opstatus", VARCHAR2(6)),
385 Column("funcidx_status", VARCHAR2(8)),
386 Column("join_index", VARCHAR2(3)),
387 Column("iot_redundant_pkey_elim", VARCHAR2(3)),
388 Column("dropped", VARCHAR2(3)),
389 Column("visibility", VARCHAR2(9)),
390 Column("domidx_management", VARCHAR2(14)),
391 Column("segment_created", VARCHAR2(3)),
392 Column("orphaned_entries", VARCHAR2(3)),
393 Column("indexing", VARCHAR2(7)),
394 Column("auto", VARCHAR2(3)),
395).alias("a_indexes")
396
397all_ind_expressions = Table(
398 "all_ind_expressions" + DB_LINK_PLACEHOLDER,
399 dictionary_meta,
400 Column("index_owner", VARCHAR2(128), nullable=False),
401 Column("index_name", VARCHAR2(128), nullable=False),
402 Column("table_owner", VARCHAR2(128), nullable=False),
403 Column("table_name", VARCHAR2(128), nullable=False),
404 Column("column_expression", LONG),
405 Column("column_position", NUMBER, nullable=False),
406).alias("a_ind_expressions")
407
408all_constraints = Table(
409 "all_constraints" + DB_LINK_PLACEHOLDER,
410 dictionary_meta,
411 Column("owner", VARCHAR2(128)),
412 Column("constraint_name", VARCHAR2(128)),
413 Column("constraint_type", VARCHAR2(1)),
414 Column("table_name", VARCHAR2(128)),
415 Column("search_condition", LONG),
416 Column("search_condition_vc", VARCHAR2(4000)),
417 Column("r_owner", VARCHAR2(128)),
418 Column("r_constraint_name", VARCHAR2(128)),
419 Column("delete_rule", VARCHAR2(9)),
420 Column("status", VARCHAR2(8)),
421 Column("deferrable", VARCHAR2(14)),
422 Column("deferred", VARCHAR2(9)),
423 Column("validated", VARCHAR2(13)),
424 Column("generated", VARCHAR2(14)),
425 Column("bad", VARCHAR2(3)),
426 Column("rely", VARCHAR2(4)),
427 Column("last_change", DATE),
428 Column("index_owner", VARCHAR2(128)),
429 Column("index_name", VARCHAR2(128)),
430 Column("invalid", VARCHAR2(7)),
431 Column("view_related", VARCHAR2(14)),
432 Column("origin_con_id", VARCHAR2(256)),
433).alias("a_constraints")
434
435all_cons_columns = Table(
436 "all_cons_columns" + DB_LINK_PLACEHOLDER,
437 dictionary_meta,
438 Column("owner", VARCHAR2(128), nullable=False),
439 Column("constraint_name", VARCHAR2(128), nullable=False),
440 Column("table_name", VARCHAR2(128), nullable=False),
441 Column("column_name", VARCHAR2(4000)),
442 Column("position", NUMBER),
443).alias("a_cons_columns")
444
445# TODO figure out if it's still relevant, since there is no mention from here
446# https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/ALL_DB_LINKS.html
447# original note:
448# using user_db_links here since all_db_links appears
449# to have more restricted permissions.
450# https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_admin005.htm
451# will need to hear from more users if we are doing
452# the right thing here. See [ticket:2619]
453all_db_links = Table(
454 "all_db_links" + DB_LINK_PLACEHOLDER,
455 dictionary_meta,
456 Column("owner", VARCHAR2(128), nullable=False),
457 Column("db_link", VARCHAR2(128), nullable=False),
458 Column("username", VARCHAR2(128)),
459 Column("host", VARCHAR2(2000)),
460 Column("created", DATE, nullable=False),
461 Column("hidden", VARCHAR2(3)),
462 Column("shard_internal", VARCHAR2(3)),
463 Column("valid", VARCHAR2(3)),
464 Column("intra_cdb", VARCHAR2(3)),
465).alias("a_db_links")
466
467all_synonyms = Table(
468 "all_synonyms" + DB_LINK_PLACEHOLDER,
469 dictionary_meta,
470 Column("owner", VARCHAR2(128)),
471 Column("synonym_name", VARCHAR2(128)),
472 Column("table_owner", VARCHAR2(128)),
473 Column("table_name", VARCHAR2(128)),
474 Column("db_link", VARCHAR2(128)),
475 Column("origin_con_id", VARCHAR2(256)),
476).alias("a_synonyms")
477
478all_objects = Table(
479 "all_objects" + DB_LINK_PLACEHOLDER,
480 dictionary_meta,
481 Column("owner", VARCHAR2(128), nullable=False),
482 Column("object_name", VARCHAR2(128), nullable=False),
483 Column("subobject_name", VARCHAR2(128)),
484 Column("object_id", NUMBER, nullable=False),
485 Column("data_object_id", NUMBER),
486 Column("object_type", VARCHAR2(23)),
487 Column("created", DATE, nullable=False),
488 Column("last_ddl_time", DATE, nullable=False),
489 Column("timestamp", VARCHAR2(19)),
490 Column("status", VARCHAR2(7)),
491 Column("temporary", VARCHAR2(1)),
492 Column("generated", VARCHAR2(1)),
493 Column("secondary", VARCHAR2(1)),
494 Column("namespace", NUMBER, nullable=False),
495 Column("edition_name", VARCHAR2(128)),
496 Column("sharing", VARCHAR2(13)),
497 Column("editionable", VARCHAR2(1)),
498 Column("oracle_maintained", VARCHAR2(1)),
499 Column("application", VARCHAR2(1)),
500 Column("default_collation", VARCHAR2(100)),
501 Column("duplicated", VARCHAR2(1)),
502 Column("sharded", VARCHAR2(1)),
503 Column("created_appid", NUMBER),
504 Column("created_vsnid", NUMBER),
505 Column("modified_appid", NUMBER),
506 Column("modified_vsnid", NUMBER),
507).alias("a_objects")
508