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:24] – [NOT owned by oracle] 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 1555: / Line 1576: @@
 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>