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:22] – [Virtual columns] 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: |
dba/oracle/basic_oracle_sql_querys.txt · Last modified: 2023/07/03 08:37 by dodger