信息收集SQL-gather_database_information.sql

col spf new_value spf noprint

define owner=ylgj_council;

select instance_name  ins_name from v$instance;
select instance_name||'_'||'&owner'||'_information_'||to_char(sysdate,'yyyymmddHH24miss')||'.html' spf from v$instance;

set linesize 200
set heading on
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spo &spf

-- version
select * from v$version;

-- instance_name
select upper(instance_name) as instance_name  from v$instance;

-- server charset
SELECT USERENV('language') database_charset FROM DUAL;

-- config for goldengate
SELECT NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
select name, value from v$spparameter where name='enable_goldengate_replication';
SELECT TABLESPACE_NAME, CONTENTS FROM DBA_TABLESPACES;
SELECT NAME FROM V$DATAFILE;

-- database size by owner
SELECT OWNER, ROUND(SUM(BYTES) / 1024 / 1024) AS "DB_Size(MB)"
  FROM DBA_SEGMENTS
 WHERE OWNER = upper('&owner') 
 AND SEGMENT_NAME IN (SELECT T2.OBJECT_NAME
                          FROM DBA_OBJECTS T2
                         WHERE T2.OBJECT_TYPE = 'TABLE')
 GROUP BY OWNER
 ORDER BY 2 DESC;

-- table count by owner 
SELECT COUNT(*) AS "Table_Count", OWNER
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = upper('&owner')
 GROUP BY OWNER
 ORDER BY 1 DESC;

--  PK AND UK
SELECT OWNER, TABLE_NAME,'No primary key or unique index' as comments
  FROM DBA_TABLES A
 WHERE NOT EXISTS (SELECT 1
          FROM DBA_CONSTRAINTS B
         WHERE B.CONSTRAINT_TYPE IN ('P', 'U')
           AND A.TABLE_NAME = B.TABLE_NAME
           AND A.OWNER = B.OWNER)
   AND OWNER = upper('&owner')
 GROUP BY OWNER, TABLE_NAME
 ORDER BY TABLE_NAME;


-- VARCHAR MORE THAN 15000 AND ONLY GET VARCHAR>1000 COLUMNS
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,'varchar in table more than 15000' as comments
  FROM DBA_TAB_COLUMNS T1
 WHERE OWNER = upper('&owner')
   AND DATA_TYPE = 'VARCHAR2'
   AND DATA_LENGTH >= 1000
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND TABLE_NAME IN (SELECT T2.TABLE_NAME
                        FROM DBA_TAB_COLUMNS T2
                       WHERE T1.OWNER = T2.OWNER
                         AND T1.TABLE_NAME = T2.TABLE_NAME
                       GROUP BY OWNER, TABLE_NAME, DATA_TYPE
                      HAVING SUM(T2.DATA_LENGTH) > 15000)
 ORDER BY T1.TABLE_NAME, T1.DATA_LENGTH;

SELECT T1.TABLE_NAME,
       T1.INDEX_NAME,
       T1.COLUMN_NAME,
       T1.COLUMN_POSITION,
       T1.COLUMN_LENGTH,
       T1.CHAR_LENGTH,
       'Index field type is too long' as commennts
  FROM DBA_IND_COLUMNS T1
 WHERE T1.INDEX_OWNER  = upper('&owner')
   AND T1.TABLE_NAME NOT LIKE 'BIN%'
   AND T1.INDEX_NAME IN
       (SELECT T2.INDEX_NAME
          FROM DBA_IND_COLUMNS T2
         WHERE T1.INDEX_OWNER = T2.INDEX_OWNER
           AND T1.TABLE_NAME = T2.TABLE_NAME
           AND T1.INDEX_NAME = T2.INDEX_NAME
         GROUP BY T2.INDEX_OWNER, T2.INDEX_NAME, T2.TABLE_NAME
        HAVING SUM(T2.COLUMN_LENGTH) > 767)
 ORDER BY T1.TABLE_NAME, T1.COLUMN_POSITION;


SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE,'List of special data types' as comments
  FROM DBA_TAB_COLUMNS
 WHERE OWNER = upper('&owner')
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND DATA_TYPE IN
       ('TIMESTAMP(7)', 'TIMESTAMP(8)', 'RAW', 'UROWID', 'LONG');


SELECT T1.OWNER, TABLE_NAME,'Special table names' as comments
  FROM DBA_TABLES T1
 WHERE T1.TABLE_NAME NOT IN
       (SELECT TABLE_NAME
          FROM DBA_TABLES T2
         WHERE T1.OWNER = T2.OWNER
           AND T1.TABLE_NAME = T2.TABLE_NAME
           AND REGEXP_LIKE(T2.TABLE_NAME, '^[A-Z0-9_]+$'))
   AND T1.OWNER = upper('&owner')

SELECT T1.OWNER,
       T1.TABLE_NAME,
       T1.COLUMN_NAME,
       T1.DATA_TYPE,
       T1.DATA_LENGTH,
       'Special field names' as comments
  FROM DBA_TAB_COLUMNS T1
 WHERE T1.COLUMN_NAME NOT IN
       (SELECT COLUMN_NAME
          FROM DBA_TAB_COLUMNS T2
         WHERE T1.OWNER = T2.OWNER
           AND T1.TABLE_NAME = T2.TABLE_NAME
           AND REGEXP_LIKE(T2.COLUMN_NAME, '^[A-Z0-9_]+$'))
   AND T1.OWNER  = upper('&owner')


-- GET table default value
SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       NULLABLE,
       COLUMN_ID,
       DATA_DEFAULT
  FROM DBA_TAB_COLS
 WHERE OWNER = UPPER('&owner')
 AND table_name NOT LIKE 'BIN%'
   AND DATA_DEFAULT IS NOT NULL
 ORDER BY TABLE_NAME, COLUMN_ID;

-- GET table comments
SELECT *
  FROM DBA_TAB_COMMENTS
 WHERE OWNER = UPPER('&owner')
   AND TABLE_NAME NOT LIKE 'BIN%'
   AND TABLE_TYPE = 'TABLE'
   AND COMMENTS IS NOT NULL;

-- GET table rows estimate
SELECT OWNER, TABLE_NAME, NUM_ROWS
  FROM DBA_TABLES
 WHERE OWNER = UPPER('&owner')
   AND TABLE_NAME NOT LIKE 'BIN%';

select owner,table_name 
from dba_tables 
where owner= upper('&owner') 
order by table_name;

spool off
exit