Sam OZ
asked on
Remove Oracle Schema
Hi Experts,
How to remove an Oracle schema from the Database ? ( Google search says - Empty the Schema and Delete . But Are there any DOS/SQL script for emptying a schema?)
Alternatively , Following is good enough for me
I have an Oracle database restored from the .dmp file . After a few months client gives a new .dmp file . How can I restore this new .dmp file now to the existing Database?
Thanks
Sam
How to remove an Oracle schema from the Database ? ( Google search says - Empty the Schema and Delete . But Are there any DOS/SQL script for emptying a schema?)
Alternatively , Following is good enough for me
I have an Oracle database restored from the .dmp file . After a few months client gives a new .dmp file . How can I restore this new .dmp file now to the existing Database?
Thanks
Sam
you can drop the required schemas by logging into the database before importing right
ASKER
Hi Nav_kum,
But I tried Drop Schema command . it didn't work . Some say you have to empty the schema before deleting it . How to do that ?
But I tried Drop Schema command . it didn't work . Some say you have to empty the schema before deleting it . How to do that ?
alternatively, you can select the schemas/tables which you want to import from the dmp file. check the below url and it lists down all options/parameters of impdp.
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
Thanks
http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
Thanks
ASKER
BTW , Experts - I am using Oracle 10g
hmmm.. login with the dba user and run the below command :
drop user <<user_name>> ;
In oracle, schema names are the same as the user names. For example, if you want to empty a schema SCOTT then run the below command :
drop user scott;
Thanks
drop user <<user_name>> ;
In oracle, schema names are the same as the user names. For example, if you want to empty a schema SCOTT then run the below command :
drop user scott;
Thanks
https://community.oracle.com/thread/507126?tstart=0 ---> just give a read through to enhance your understanding of the users/schemas
ASKER
Hi Nav,
I got a fair idea of Schema and Users . The issue is when I try to drop user .. It says " Unable to drop a User in use " ..... I am able to drop another users ... But I don't really get an idea of what really locks my user .. There is no application making use of the schema
I got a fair idea of Schema and Users . The issue is when I try to drop user .. It says " Unable to drop a User in use " ..... I am able to drop another users ... But I don't really get an idea of what really locks my user .. There is no application making use of the schema
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This might already be in some of the links above (I didn't open them).
You might also need to specify cascade on the drop user command when they own objects:
drop user some_user cascade;
You likely will then need to recreate the user before import if the export is just the schema.
You might also need to specify cascade on the drop user command when they own objects:
drop user some_user cascade;
You likely will then need to recreate the user before import if the export is just the schema.
To drop a user/schema requires that account to be not in use, and to have DBA privilege to execute the DROP command. Is that true in your situation?
Does the dumpfile contain more than the one schema's tables and similar objects? If you can provide the imp or impdp script, without the user/password.
The following is an example of using the SCHEMAS parameter. You can create the expdat.dmp file used in this example by running the example provided for the Export SCHEMAS parameter. See SCHEMAS.
> impdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp
Does the dumpfile contain more than the one schema's tables and similar objects? If you can provide the imp or impdp script, without the user/password.
The following is an example of using the SCHEMAS parameter. You can create the expdat.dmp file used in this example by running the example provided for the Export SCHEMAS parameter. See SCHEMAS.
> impdp hr/hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp