5 Replies Latest reply: Nov 21, 2012 9:37 AM by khallas301 RSS

    Schema refresh...

    khallas301
      Hi All,

      I am working on this schema refresh project using expdp/impdb with db v.11.

      I have wasted enough time (1 full day) on working on couple of issues then finally decide to take help of OTN users and see if we have best and fast way to do it.

      Q:
      1. I want to generate dynamic drop script to delete tables like '%100%', '%backup%','%ABC%' from user schema but not able to figure out way to do it.. I can do it manually generate 1 drop statement for each table like option but if 50 tables are there then it will take too much manual work which I am trying to ignore?

      2. Best way to use EXCLUDE parameter in expdp para file to exclude tables like something like '%100%', '%backup%','%ABC%' : EXCLUDE=table: 'like '%100%', '%backup%','%ABC%''

      3. Generate dynamic script for dropping all user objects

      4. Stop all db users to access particular schema while import is happening?

      5. For consistent export is below parameter right?

      flashback_time="to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')"


      Thanks in advance for looking into my problems.

      changed Q.4 from export to import
      added Q.5
        • 1. Re: Schema refresh...
          vlethakula
          Q:
          1. I want to generate dynamic drop script to delete tables like '%100%', '%backup%','%ABC%' from user schema but not able to figure out way to do it.. I can do it manually generate 1 drop statement for each table like option but if 50 tables are there then it will take too much manual work which I am trying to ignore?

          A: select ' drop table '|| owner||'.'|| table_name ||' ;' from dba_tables where table_name like '%100%' and owner='owner'
          union all
          select ' drop table '|| owner||'.'|| table_name ||' ;' from dba_tables where table_name like '%backup%' and owner='owner'
          union all
          select ' drop table '|| owner||'.'|| table_name ||' ;' from dba_tables where table_name like '%ABC%' and owner='owner'

          Q:
          Best way to use EXCLUDE parameter in expdp para file to exclude tables like something like '%100%', '%backup%','%ABC%' : EXCLUDE=table: 'like '%100%', '%backup%','%ABC%''

          A: Get the list of tables from database having like '%100%','%backup','%ABC%' . Prepare parfile and in exclude parameter give list of table names using IN clause

          Q:Generate dynamic script for dropping all user objects

          a: select ' drop ' || object_type || ' '||owner||'.'||object_name ||';' from dba_objects where owner='SCHEMANAME';

          4. Stop all db users to access particular schema while import is happening?

          A: Open the database in restrict mode ; or stop the listener if Listener also listens for this service(If there are multiple db's accessing same listener then do not do it)

          5. Yes
          • 2. Re: Schema refresh...
            khallas301
            Thanks vlethakula...

            It does solves most of my problem and it was really helpful...

            not sure about 2 & 4 answer...

            for 2. I am planning to use below but still not tested...
            exclude=table:"IN(select table_name from user_tables where table_name like '%T20%' or table_name like '%backup%')

            4. Stopping user accessing db... I cannot stop listener..

            still searching other option to lock schema so no user will access schema during impdp...
            • 3. Re: Schema refresh...
              vlethakula
              You can try locking the user and then import

              Here:


              Test@Test1> alter user locktest account lock;

              User altered.

              Test@Test1> connect locktest/locktest
              ERROR:
              ORA-28000: the account is locked

              impdp system/k5rnel directory=dataexp dumpfile=lock.dmp remap_schema=test1:locktest

              Processing object type SCHEMA_EXPORT/USER
              ORA-31684: Object type USER:"LOCKTEST" already exists
              Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
              Processing object type SCHEMA_EXPORT/ROLE_GRANT
              Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
              Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
              Processing object type SCHEMA_EXPORT/TABLE/TABLE
              Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
              . . imported "LOCKTEST"."BIDIRREP" 5.226 KB 1 rows
              . . imported "LOCKTEST"."AUDITTEST" 0 KB 0 rows
              . . imported "LOCKTEST"."AUDTEST2" 0 KB 0 rows
              . . imported "LOCKTEST"."BIDI" 0 KB 0 rows
              . . imported "LOCKTEST"."LARGE_TABLE":"DUMMY" 0 KB 0 rows
              . . imported "LOCKTEST"."T" 0 KB 0 rows
              . . imported "LOCKTEST"."T2" 0 KB 0 rows
              . . imported "LOCKTEST"."TEST" 0 KB 0 rows
              Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
              Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
              • 4. Re: Schema refresh...
                khallas301
                But if other users have got access to schema then they will still able to connect?

                thanks for your efforts in providing example..
                • 5. Re: Schema refresh...
                  khallas301
                  Any idea on how to stop all users from accessing particular schema without bringing listener down?