Oracle Tuning Table And Object Information Related

-- ---------------------------------------- 0.7 Quick Table and object information -- --------------------------------------SELECT distinct substr(t.owner, 1, 25), substr(t.table_name,1,50),substr(t.tablespace_name,1,20), t.chain_cnt, t.logging, s.relative_fno FROM dba_tables t, dba_segments s WHERE t.owner not in ('SYS','SYSTEM', 'OUTLN','DBSNMP','WMSYS','ORDSYS','ORDPLUGINS','MDSYS','CTXSYS','XDB') AND t.table_name=s.segment_name AND s.segment_type='TABLE' AND s.segment_name like 'CI_PAY%'; SELECT substr(segment_name, 1, 30), segment_type, substr(owner, 1, 10), extents, initial_extent, next_extent, max_extents FROM dba_segments WHERE extents > max_extents - 100 AND owner not in ('SYS','SYSTEM'); SELECT segment_name, owner, tablespace_name, extents FROM dba_segments WHERE owner='SALES' -- you use the correct schema here and extents > 700;

SELECT owner, substr(object_name, 1, 30), object_type, created, last_ddl_time, status FROM dba_objects where OWNER='CLASSIFIED';

SELECT owner, substr(object_name, 1, 30), object_type, created, last_ddl_time, status FROM dba_objects WHERE created > SYSDATE-1; SELECT owner, substr(object_name, 1, 30), object_type, created, last_ddl_time, status FROM dba_objects WHERE status='INVALID'; Compare 2 owners: ----------------select table_name from dba_tables where owner='MIS_OWNER' and table_name not in (SELECT table_name from dba_tables where OWNER='MARPAT'); Table and column information: ----------------------------select substr(table_name, 1, 3) schema, table_name, column_name, substr(data_type,1 ,1) data_type from user_tab_columns where COLUMN_NAME='ENV_ID' where table_name like 'ALG%'

or table_name like 'STG%' or table_name like 'ODS%' or table_name like 'DWH%' or table_name like 'MKM%' order by decode(substr(table_name, 1, 3), 'ALG', 10, 'STG', 20, 'ODS', 30, 'DWH', 40, 'MKM', 50, 60), table_name , column_id

Check on existence of JServer: -----------------------------select count(*) from all_objects where object_name = 'DBMS_JAVA'; should return a count of 3