Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Remove Oracle Schema

Posted on 2014-03-26
11
Medium Priority
?
646 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:Sam OZ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39955332
you can drop the required schemas by logging into the database before importing right
0
 

Author Comment

by:Sam OZ
ID: 39955337
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 ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39955338
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Sam OZ
ID: 39955339
BTW , Experts - I am using Oracle 10g
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39955342
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
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39955343
https://community.oracle.com/thread/507126?tstart=0 ---> just give a read through to enhance your understanding of the users/schemas
0
 

Author Comment

by:Sam OZ
ID: 39955365
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
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1400 total points
ID: 39955372
oh..ok. thanks. you need to check what sessions are currently there from the V$session dict view

Can you also please post the error along with the exact oracle error number to be precise for me to understand the exact cause.

check this - https://community.oracle.com/thread/2283912?tstart=0
0
 
LVL 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 600 total points
ID: 39955384
You'd have to kill the session(s) beforehand (of the connected users) and then drop them, as shown here:

http://javaworks.wordpress.com/2009/10/29/oracle-how-to-drop-a-user-who-is-connected-forcefully/

Good luck ;-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39956967
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.
0
 
LVL 23

Expert Comment

by:David
ID: 39957577
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question