'골든게이트 사전점검'에 해당되는 글 1건

-- Example of running the script:

-- sqlplus <userid>/<pw> @schemaCheckOracle.sql

set null "NULL VALUE"

set feedback off

set heading off

set linesize 132 

set pagesize 9999

set echo off

set verify off

col table_name for a30

col column_name for a30

col data_type for a15

col object_type for a20

col constraint_type_desc for a30

--spool schemaCheckOracle.&&schema_name.out

ACCEPT schema_name char prompt 'Enter the Schema Name > '

variable b0 varchar2(50)

exec :b0 := upper('&schema_name');

spool schemaCheckOracle.&&schema_name..out

SET Heading off

SELECT '------ System Info for: '||:b0 

FROM dual;

set heading on

select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual

/

select banner from v$version

/

SET Heading off

SELECT '------ Database Level Supplemental Log Check - 9i and 10g: ' 

FROM dual;

SET Heading on

SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI 

FROM V$DATABASE

/ 

select name, log_mode "LogMode", 

supplemental_log_data_min "SupLog: Min", supplemental_log_data_pk "PK",

supplemental_log_data_ui "UI", force_logging "Forced",

supplemental_log_data_fk "FK", supplemental_log_data_all "All",

to_char(created, 'MM-DD-YYYY HH24:MI:SS') "Created"

from v$database

/

select 

platform_name

from v$database

/

set heading off

SELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of OGG ' 

FROM dual;

SET Heading on

select

TABLESPACE_NAME,

DEF_TAB_COMPRESSION

from DBA_TABLESPACES

where 

DEF_TAB_COMPRESSION <> 'DISABLED';

set heading off

SELECT '------ Distinct Object Types and their Count in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT object_type, count(*) total

FROM all_objects

WHERE OWNER = :b0

GROUP BY object_type

/

SET Heading off

SELECT '------ Distinct Column Data Types and their Count in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT data_type, count(*) total

FROM all_tab_columns

WHERE OWNER = :b0 

GROUP BY data_type

/

SET Heading off

SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0 

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2 

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE')

/

SET Heading off

SELECT '------ Tables with no PK or UI and rowsize > 1M in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tab_columns

WHERE owner = :b0

group by table_name

HAVING sum(data_length) > 1000000

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2 

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE')

/

set heading off

SELECT '------ Tables with NOLOGGING setting ' FROM dual;

SELECT '------ This may cause problems with missing data down stream. Schema: ' ||:b0 FROM dual;

set heading on

select owner, table_name, ' ' "PARTITION_NAME", ' ' "PARTITIONING_TYPE", logging from DBA_TABLES

where logging <> 'YES' and OWNER = :b0

union

select owner, table_name, ' ', partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tables

where DEF_LOGGING != 'YES' and owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_PARTITIONS

where logging <> 'YES' and table_owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_SUBPARTITIONS

where logging <> 'YES' and table_owner <> :b0

;

set heading off

SELECT '------ Tables with Deferred constraints.Deferred constraints may cause TRANDATA to chose an incorrect Key ' FROM dual;

SELECT '------ Tables with Deferred constraints should be added using KEYCOLS in the trandata statement. Schema: ' ||:b0 FROM dual;

set heading on

SELECT c.TABLE_NAME,

c.CONSTRAINT_NAME,

c.CONSTRAINT_TYPE,

c.DEFERRABLE,

c.DEFERRED,

c.VALIDATED,

c.STATUS,

i.INDEX_TYPE,

c.INDEX_NAME,

c.INDEX_OWNER

FROM dba_constraints c,

dba_indexes i

WHERE

i.TABLE_NAME = c.TABLE_NAME

AND i.OWNER = c.OWNER

AND c.owner = :b0

AND c.DEFERRED = 'DEFERRED';

set heading off

SELECT '------ Tables Defined with Rowsize > 2M in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT table_name, sum(data_length) row_length_over_2M

FROM all_tab_columns

WHERE OWNER = :b0 

GROUP BY table_name

HAVING sum(data_length) > 2000000

/

SET Heading off

SELECT '------ Tables that will Fail Add Trandata (Only an issue for Oracle versions below Oracle 10G) in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM dba_tab_columns

WHERE owner = :b0 

AND column_id > 32

AND table_name in

(SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0 

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE'))

/

SET Heading off

SELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0 

AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')

/

SET Heading off

SELECT '------ Tables With Columns of UNSUPPORTED Datatypes in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0 

AND (data_type in ('ORDDICOM', 'BFILE', 'TIMEZONE_REGION', 'BINARY_INTEGER', 'PLS_INTEGER', 'UROWID', 'URITYPE', 'MLSLABEL', 'TIMEZONE_ABBR', 'ANYDATA', 'ANYDATASET', 'ANYTYPE')

or data_type like 'INTERVAL%')

/

SET Heading off

SELECT '----- Tables with Compressed data is not supported - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COMPRESSION

FROM all_all_tables

WHERE OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SELECT TABLE_NAME, COMPRESSION

FROM ALL_TAB_PARTITIONS

WHERE TABLE_OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SET Heading off

SELECT '----- Cluster (DML and DDL supported for 9i or later) or Object Tables (DML supported for 10G or later, no DDL) - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, CLUSTER_NAME, TABLE_TYPE 

FROM all_all_tables

WHERE OWNER = :b0 

AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)

/

SET Heading off

SELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, IOT_TYPE, TABLE_TYPE 

FROM all_all_tables

WHERE OWNER = :b0 

AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)

/

SET Heading off

SELECT '------ Tables with Domain or Context Indexes - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, index_name, index_type 

FROM dba_indexes WHERE OWNER = :b0

and index_type = 'DOMAIN'

/

SET Heading off

SELECT '------ Types of Constraints on the Tables in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R', 

'REFERENTIAL') constraint_type_desc, count(*) total

FROM all_constraints

WHERE OWNER = :b0 

GROUP BY constraint_type

/

SET Heading off

SELECT '------ Cascading Deletes on the Tables in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT table_name, constraint_name

FROM all_constraints

WHERE OWNER = :b0 and constraint_type = 'R' and delete_rule = 'CASCADE'

/

SET Heading off

SELECT '------ Tables Defined with Triggers in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT table_name, COUNT(*) trigger_count

FROM all_triggers

WHERE OWNER = :b0 

GROUP BY table_name

/

SET Heading off

SELECT '------ Performance issues - Reverse Key Indexes Defined in the Schema: '||:b0

FROM dual;

col Owner format a10

col TABLE_OWNER format a10

col INDEX_TYPE format a12

SET Heading on

select 

OWNER, 

INDEX_NAME,

INDEX_TYPE, 

TABLE_OWNER,

TABLE_NAME, 

TABLE_TYPE, 

UNIQUENESS,

CLUSTERING_FACTOR,

NUM_ROWS,

LAST_ANALYZED,

BUFFER_POOL

from dba_indexes

where index_type = 'NORMAL/REV'

And OWNER = :b0

/

SET Heading off

SELECT '------ Sequence numbers: '||:b0

FROM dual;

COLUMN SEQUENCE_OWNER FORMAT a15

COLUMN SEQUENCE_NAME FORMAT a30

COLUMN INCR FORMAT 999

COLUMN CYCLE FORMAT A5

COLUMN ORDER FORMAT A5

SET Heading on

SELECT SEQUENCE_OWNER,

SEQUENCE_NAME,

MIN_VALUE,

MAX_VALUE,

INCREMENT_BY INCR,

CYCLE_FLAG CYCLE,

ORDER_FLAG "ORDER",

CACHE_SIZE,

LAST_NUMBER

FROM DBA_SEQUENCES

WHERE SEQUENCE_OWNER=UPPER(:b0)

/

set linesize 132

col "Avg Log Size" format 999,999,999

select sum (BLOCKS) * max(BLOCK_SIZE)/ count(*)"Avg Log Size" From gV$ARCHIVED_LOG;

Prompt Table: Frequency of Log Switches by hour and day

SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) DAY, 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'00',1,0)),'99') "00", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'01',1,0)),'99') "01", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'02',1,0)),'99') "02", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'03',1,0)),'99') "03", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'04',1,0)),'99') "04", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'05',1,0)),'99') "05", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'06',1,0)),'99') "06", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'07',1,0)),'99') "07", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'08',1,0)),'99') "08", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'09',1,0)),'99') "09", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'10',1,0)),'99') "10", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'11',1,0)),'99') "11", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'12',1,0)),'99') "12", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'13',1,0)),'99') "13", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'14',1,0)),'99') "14", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'15',1,0)),'99') "15", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'16',1,0)),'99') "16", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'17',1,0)),'99') "17", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'18',1,0)),'99') "18", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'19',1,0)),'99') "19", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'20',1,0)),'99') "20", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'21',1,0)),'99') "21", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'22',1,0)),'99') "22", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'23',1,0)),'99') "23" 

FROM V$LOG_HISTORY 

GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) 

order by 1;

SET Heading off

SELECT '------ Summary of log volume processed by day for last 7 days: '

FROM dual;

SET Heading on

select to_char(first_time, 'mm/dd') ArchiveDate,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'mm/dd')

order by to_char(first_time, 'mm/dd');

/

SET Heading off

SELECT '------ Summary of log volume processed per hour for last 7 days: ' 

FROM dual;

SET Heading on

select to_char(first_time, 'MM-DD-YYYY') ArchiveDate, 

to_char(first_time, 'HH24') ArchiveHour,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')

order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');

/ 

set heading off

select '* This output may be found in file: schemaCheckOracle.&&schema_name..out' from dual

/

spool off

undefine b0

-- exit