Differences

This shows you the differences between two versions of the page.

--- dba:oracle:basic_oracle_sql_querys [2023/07/03 08:24] – [NOT owned by oracle] dodger
+++ dba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) – dodger
@@ Line 1400: / Line 1400: @@
 </code>
 ====== [SECTION]  Objects======
+===== 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
+/
+</code>
+==== 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 (
+         'SYSTEM','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW',
+         'OLAPSYS','ORDDATA','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN',
+         'SI_INFORMTN_SCHEMA','SYS','SYSMAN','WK_TEST','WKSYS','WKPROXY',
+         'WMSYS','XDB','APEX_PUBLIC_USER','DIP','FLOWS_020100','FLOWS_030000',
+         'FLOWS_040100','FLOWS_010600','FLOWS_FILES','MDDATA','ORACLE_OCM',
+         'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','XS$NULL','PERFSTAT',
+         'SQLTXPLAIN','DMSYS','TSMSYS','WKSYS','APEX_040000','APEX_040200',
+         'DVSYS','OJVMSYS','GSMADMIN_INTERNAL','APPQOSSYS','DVSYS','DVF',
+         'AUDSYS','APEX_030200','MGMT_VIEW','ODM','ODM_MTR','TRACESRV','MTMSYS',
+         'OWBSYS_AUDIT','WEBSYS','WK_PROXY','OSE$HTTP$ADMIN',
+         'AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
+         'DBMS_PRIVILEGE_CAPTURE','CSMIG','MGDSYS','SDE','DBSFWUSER'
+      )
+  group by type_name
+  )
+/
+</code>
 ===== Compilar =====
-==== Compilar un objeto ====
+Compile an object:
 <code sql>ALTER <OBJECT_TYPE> [schema.]package_name COMPILE ;
 </code>
@@ Line 1412: / Line 1463: @@
 </code>
-==== Compilar todos los objects invalidos ====
+Compile ALL objects (invalid):
 See:
 [[dba:oracle:scripts:recompile_it_all|[SCRIPT] Recompile all database objects]]