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"