Thursday, August 11, 2005

Archive_Schema

This procedure (which by the way can EASILY be converted to a script) is to arcive a set of selected schemas. To do this, the user you login as (you shouldn't use SYS if you can avoid it... similar principle as root in the *NIX world) has to have the SELECT_CATALOG_ROLE granted. Note the AUTHID CURRENT_USER in the CREATE OR REPLACE line. This is also part of the security features for this script to allow viewing the DDL of other schema objects provided by Oracle and PL/SQL.

CREATE OR REPLACE PROCEDURE Archive_Schema( fileDirectory IN VARCHAR2, schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) AUTHID CURRENT_USER IS
mCLOB CLOB;
mOutputFile UTL_FILE.FILE_TYPE;
mOwner VARCHAR2(30);
mObjectType VARCHAR2(18);
mObjectName VARCHAR2(30);
mDDLCursorID INTEGER;
mReturnValue INTEGER;
mErrCode NUMBER;

mDBHostName VARCHAR(64);
mDBInstanceName VARCHAR(16);
mDBInstanceNumber NUMBER;
mDBVersion VARCHAR2(17);
mDBStartupTime DATE;

mTypeSchemaArchive typeSchemaArchive := typeSchemaArchive();

PROCEDURE GetDBInfo IS
BEGIN
DBMS_SQL.PARSE( mDDLCursorID, 'SELECT instance_number, instance_name, host_name, version, startup_time FROM v$instance', DBMS_SQL.NATIVE );

DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 1, mDBInstanceNumber );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 2, mDBInstanceName, 16 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 3, mDBHostName, 64 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 4, mDBVersion, 17 );
DBMS_SQL.DEFINE_COLUMN( mDDLCursorID, 5, mDBStartupTime );

mReturnValue := DBMS_SQL.EXECUTE_AND_FETCH( mDDLCursorID );

DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 1, mDBInstanceNumber );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 2, mDBInstanceName );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 3, mDBHostName );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 4, mDBVersion );
DBMS_SQL.COLUMN_VALUE( mDDLCursorID, 5, mDBStartupTime );
END;
--END GetDBInfo

PROCEDURE OpenOutputFile( fileDirectory IN VARCHAR2, schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) IS
filename VARCHAR2(255);

BEGIN
filename := mDBInstancename || '_' || REPLACE( schemaList, delimiter, '_' ) || '_' || TO_CHAR( SYSDATE, 'yyyymmddhh24miss' ) || '.sql';
mOutputFile := UTL_FILE.FOPEN( fileDirectory, filename, 'w', 32767 );
END;
--END OpenOutputFile

PROCEDURE ParseSchemaList( schemaList IN VARCHAR2, delimiter IN VARCHAR2 DEFAULT ',' ) IS
-- declare and initialize PL/SQL table based on pre-created type
-- variables needed for parsing
offset NUMBER DEFAULT 1;
overallLength NUMBER DEFAULT LENGTH( schemaList ); -- The length of the input string.
stringLength NUMBER; -- The length of the current chunk.

BEGIN
-- load temp table type from delimited list

WHILE offset < overallLength LOOP
stringLength := INSTR( schemaList, delimiter, offset );
IF stringLength = 0 THEN
stringLength := overallLength + 1;
END IF;

mTypeSchemaArchive.EXTEND;
mTypeSchemaArchive( mTypeSchemaArchive.COUNT ) := SUBSTR( schemaList, offset, stringLength - offset );

offset := stringLength + 1;
END LOOP;
END;
--END ParseSchemaList;

PROCEDURE PrintOwnerHeader( p_Owner IN VARCHAR2 ) IS
BEGIN
UTL_FILE.NEW_LINE( mOutputFile, 2 );
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, 'SCHEMA: %s', p_Owner );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
END;
-- END PrintOwnerHeader;

PROCEDURE PrintObjectHeader( p_owner IN VARCHAR2, p_objectType IN VARCHAR2 ) IS
BEGIN
UTL_FILE.NEW_LINE( mOutputFile, 2 );
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, '%s SCHEMA OBJECT: %s', p_owner, p_objectType );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
END;
-- END PrintObjectHeader;

PROCEDURE PrintFileHeader IS
BEGIN
UTL_FILE.PUT_LINE( mOutputFile, '/*==================================' );
UTL_FILE.PUTF( mOutputFile, 'Select database schemas as of: %s', TO_CHAR( SYSDATE, 'dd-mon-yyyy hh24:mi:ss' ) );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'HOST NAME: %s', mDBHostName );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'INSTANCE NAME: %s', mDBInstanceName );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'INSTANCE NUMBER: %s', TO_CHAR( mDBInstanceNumber ) );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'DB VERSION: %s', mDBVersion );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUTF( mOutputFile, 'DB STARTUP TIME: %s', TO_CHAR( mDBStartupTime, 'dd-mon-yyyy hh24:mi:ss' ) );
UTL_FILE.NEW_LINE( mOutputFile, 2 );

UTL_FILE.PUT_LINE( mOutputFile, 'Selected Schemas:' );

FOR schemas IN ( SELECT COLUMN_VALUE FROM TABLE(CAST(mTypeSchemaArchive AS typeSchemaArchive)) ) LOOP
UTL_FILE.PUTF( mOutputFile, ' %s', schemas.COLUMN_VALUE );
UTL_FILE.NEW_LINE( mOutputFile, 1 );
END LOOP;

UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.PUT_LINE( mOutputFile, '==================================*/' );
UTL_FILE.NEW_LINE( mOutputFile, 2 );
END;
-- END PrintFileHeader;

BEGIN
mDDLCursorID := DBMS_SQL.OPEN_CURSOR;

GetDBInfo;

OpenOutputFile( fileDirectory, schemaList, delimiter );

ParseSchemaList( schemaList, delimiter );

PrintFileHeader;

DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );

FOR schemas IN ( SELECT owner, object_type, object_name FROM ALL_OBJECTS WHERE owner IN ( SELECT COLUMN_VALUE FROM TABLE(CAST(mTypeSchemaArchive AS typeSchemaArchive)) ) ORDER BY owner, object_type, object_name ) LOOP
BEGIN
IF mOwner IS NULL THEN
mOwner := schemas.owner;
mObjectType := schemas.object_type;

PrintOwnerHeader( mOwner );
PrintObjectHeader( mOwner, mObjectType );
ELSE
IF mOwner <> schemas.owner THEN
mOwner := schemas.owner;
mObjectType := schemas.object_type;

PrintOwnerHeader( mOwner );
PrintObjectHeader( mOwner, mObjectType );

ELSIF mObjectType <> schemas.object_type THEN
mObjectType := schemas.object_type;

PrintObjectHeader( mOwner, mObjectType );

END IF;
END IF;

mCLOB := DBMS_METADATA.GET_DDL( mObjectType, schemas.object_name, mOwner );

UTL_FILE.PUT( mOutputFile, DBMS_LOB.SUBSTR( mCLOB, DBMS_LOB.GETLENGTH( mCLOB ), 1 ) );
UTL_FILE.FFLUSH( mOutputFile );
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.PUTF( mOutputFile, 'Error on %s: %s.%s: %s', mObjectType, mOwner, schemas.object_name, SQLERRM(SQLCODE));
UTL_FILE.NEW_LINE( mOutputFile, 1 );
UTL_FILE.FFLUSH( mOutputFile );
END;
END LOOP;

UTL_FILE.FCLOSE( mOutputFile );

DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'DEFAULT', TRUE );

DBMS_SQL.CLOSE_CURSOR( mDDLCursorID );
END;
/

Here's a sampling of the output from this:
/*==================================
Select database schemas as of: 11-aug-2005 15:52:32
HOST NAME: gilligan
INSTANCE NAME: gilligan
INSTANCE NUMBER: 1
DB VERSION: 9.2.0.5.0
DB STARTUP TIME: 10-aug-2005 14:33:41

Selected Schemas:
PSG
PERFSTAT

==================================*/




/*==================================
SCHEMA: PERFSTAT
==================================*/


/*==================================
PERFSTAT SCHEMA OBJECT: INDEX
==================================*/

CREATE UNIQUE INDEX "PERFSTAT"."STATS$BG_EVENT_SUMMARY_PK" ON "PERFSTAT"."STATS$BG_EVENT_SUMMARY" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "EVENT")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS"

CREATE UNIQUE INDEX "PERFSTAT"."STATS$BUFFER_POOL_STATS_PK" ON "PERFSTAT"."STATS$BUFFER_POOL_STATISTICS" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS"

CREATE UNIQUE INDEX "PERFSTAT"."STATS$DATABASE_INSTANCE_PK" ON "PERFSTAT"."STATS$DATABASE_INSTANCE" ("DBID", "INSTANCE_NUMBER", "STARTUP_TIME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS"

CREATE UNIQUE INDEX "PERFSTAT"."STATS$DB_CACHE_ADVICE_PK" ON "PERFSTAT"."STATS$DB_CACHE_ADVICE" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "ID", "BUFFERS_FOR_ESTIMATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS"

CREATE UNIQUE INDEX "PERFSTAT"."STATS$DLM_MISC_PK" ON "PERFSTAT"."STATS$DLM_MISC" ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "STATISTIC#")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS"