dba:oracle:basic_oracle_sql_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:oracle:basic_oracle_sql_querys [2023/07/03 08:20] – [Columnas de las tablas] dodger | dba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) – dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== [DOC] Oracle basic queries ====== | ====== [DOC] Oracle basic queries ====== | ||
+ | |||
+ | ====== [SECTION] PRE-basics ====== | ||
+ | ===== NOT owned by oracle ===== | ||
+ | This is really useful to remove " | ||
+ | <code sql> | ||
+ | and NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | </ | ||
+ | |||
Line 1379: | Line 1400: | ||
</ | </ | ||
====== [SECTION] | ====== [SECTION] | ||
+ | |||
+ | ===== UDT: User defined types ===== | ||
+ | |||
+ | ==== List UDTs ==== | ||
+ | <code sql> | ||
+ | col type_name format a40 | ||
+ | col type_oid format a40 | ||
+ | col SUPERTYPE_NAME format a40 | ||
+ | col SUPERTYPE_OWNER format a40 | ||
+ | select owner, | ||
+ | type_name, | ||
+ | type_oid, | ||
+ | SUPERTYPE_OWNER, | ||
+ | SUPERTYPE_NAME | ||
+ | from dba_types | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | ==== List UDTs usage in tables ==== | ||
+ | <code sql> | ||
+ | col type_name format a40 | ||
+ | col type_oid format a40 | ||
+ | |||
+ | select owner, | ||
+ | table_name, | ||
+ | column_name, | ||
+ | data_type, | ||
+ | virtual_column | ||
+ | from dba_tab_cols | ||
+ | where data_type in ( | ||
+ | select | ||
+ | type_name | ||
+ | from dba_types | ||
+ | where NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
+ | group by type_name | ||
+ | ) | ||
+ | / | ||
+ | </ | ||
===== Compilar ===== | ===== Compilar ===== | ||
- | ==== Compilar un objeto ==== | + | Compile an object: |
<code sql> | <code sql> | ||
</ | </ | ||
Line 1391: | Line 1463: | ||
</ | </ | ||
- | ==== Compilar todos los objects | + | Compile ALL objects |
See: | See: | ||
[[dba: | [[dba: | ||
Line 1572: | Line 1643: | ||
where VIRTUAL_COLUMN=' | where VIRTUAL_COLUMN=' | ||
and OWNER in ( ' | and OWNER in ( ' | ||
+ | order by owner, table_name, column_name | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Not owned by system users: | ||
+ | <code sql> | ||
+ | COL OWNER FORMAT A30; | ||
+ | COL TABLE_NAME FORMAT A30; | ||
+ | COL COLUMN_NAME FORMAT A30; | ||
+ | COL virtual_column FORMAT A5; | ||
+ | |||
+ | select owner, | ||
+ | table_name, | ||
+ | column_name, | ||
+ | virtual_column | ||
+ | from dba_tab_cols | ||
+ | where VIRTUAL_COLUMN=' | ||
+ | and NOT owner in ( | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ) | ||
order by owner, table_name, column_name | order by owner, table_name, column_name | ||
/ | / |
dba/oracle/basic_oracle_sql_querys.1688372418.txt.gz · Last modified: 2023/07/03 08:20 by dodger