I need to copy... by any means, a schema of my database in that same database, let's say, I have SC1 and I have to duplicate it as SC2 without the data contained in SC1 (in the same database).
How could that process be carried out?
I am using Oracle Database 11gR2.
2 Answers 2
Code Repository
You should have all of your CREATE scripts in a code repository. If not, you should attempt to fix that.
Data Pump
There are ways to expdp the tables without data.
SQLDeveloper
Using the Cart feature, you can get the DDLs for your Code Repository.
https://www.thatjeffsmith.com/archive/2011/11/introducing-the-sql-developer-shopping-cart/
If using Toad: Database -> Export -> Generate Schema Script
You can choose to copy an entire schema (SC1, in your case) and then ensure that it has
Then log in as the SC2 schema user (or as DBA/SYS) and deploy the SC2 schema objects using the "deploy all" script that comes with the export. If any of the objects use functions/procedures from other schemas, you'll need to ensure that either the correct permissions are set up, or you run this as DBA/SYS.
If using SQL Developer: Tools -> Database export
Choose the source Database and Ensure "Export DDL" is checked (if you want to copy the objects as well).
Then, select which objects you want to copy.
In the "Specify objects" area, Click "More" and choose the source schema. Then select the objects and click the right arrow, or click the double right arrow to select all objects.
Keep moving through the wizard and it should export multiple scripts to run. Run them all and you've got yourself a copy.
NOTE! I highly recommend you don't do this in production. I'm hoping you're not trying to create SC2 in a production Database.