Differences

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

--- dba:oracle:basic_oracle_sql_querys [2023/06/19 16:39] – [Hard parse] dodger
+++ dba:oracle:basic_oracle_sql_querys [2023/07/03 08:37] (current) – dodger
@@ Line 1: / Line 1: @@
 ====== [DOC] Oracle basic queries ======
+====== [SECTION] PRE-basics ======
+===== NOT owned by oracle =====
+This is really useful to remove "noise" on queries:
+<code sql>
+and 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'
+    )
+</code>
@@ Line 1379: / 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 1391: / Line 1463: @@
 </code>
-==== Compilar todos los objects invalidos ====
+Compile ALL objects (invalid):
 See:
 [[dba:oracle:scripts:recompile_it_all|[SCRIPT] Recompile all database objects]]
@@ Line 1555: / Line 1626: @@
 WHERE OWNER = '<USERNAME>'
 ORDER BY OWNER,TABLE_NAME ;
+</code>
+===== Virtual columns =====
+<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='YES'
+and OWNER in ( 'USERNAME1', 'USER2' )
+order by owner, table_name, column_name
+/
+</code>
+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='YES'
+and 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'
+    )
+order by owner, table_name, column_name
+/
 </code>