This discussion is archived
5 Replies Latest reply: Nov 21, 2012 7:37 AM by khallas301 RSS

Schema refresh...

khallas301 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Any idea on how to stop all users from accessing particular schema without bringing listener down?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points