What's the best way to recreate an Oracle database?
Oracle 11gR2 (x86 Windows):
I have a db with 250 tables with indexes and constraints. I need to re-create these tables, indexes and constraints in a new db and load the data. I need to know how to do the following in SQL Plus and / or SQL Developer, unless there is a magic utility out there that could automate the whole thing. Thanks in advance!
-
Unload (export) all data from 250 tables.
-
Create a sql script file containing instructions
CREATE TABLE
for 250 tables. -
Create a sql script file containing instructions
CREATE INDEX
for 250 tables. -
Create a sql script file containing instructions
ALTER TABLE ADD CONSTRAINT
for 250 tables. -
Run script to create tables in new db.
-
Load the exported data into tables in a new db.
-
Run the script to create all indexes.
-
Run the script to add all constraints.
EDIT: I'm connected to a remote desktop that links to the original db on Windows Server 2008. The remote client only has an Oracle client. For security reasons, I am not allowed to directly link from my local machine to Win Server, so that I can dump the entire source db to the remote machine and then pin it to the local target machine? I am trying to replicate an entire db to my computer.
source to share
Starting with Oracle 10g, you can use Data Pump command-line clients expdb
and impdb
to export / import data and / or schema from one database to another. In fact, these two command line utilities are just wrappers that "use the procedures provided in the PL / SQL package DBMS_DATAPUMP to execute export and import commands using the parameters entered on the command line." (quoted in Oracle documentation)
Considering your needs, you need to create a directory and then generate a complete dump of your database with expdb
:
SQL> CREATE OR REPLACE DIRECTORY dump_dir AS '/path/to/dump/folder/';
sh$ expdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log
Since the dump is written using some binary format, you will have to use the appropriate import utility to (re) import your DB. Basically replacing expdb
with impdb
in the above command:
sh$ impdp system@db10g full=Y directory=DUMP_DIR dumpfile=db.dmp logfile=db.log
For a simple table dump, use this version:
sh$ expdp sylvain@db10g tables=DEPT,EMP directory=DUMP_DIR dumpfile=db.dmp logfile=db.log
As you noticed, you can use it with your standard user account if you have access to the given directory ( GRANT READ, WRITE ON DIRECTORY dump_dir TO sylvain;
).
For detailed usage explanations, see
source to share
If you can create a database link from your local database to the one that currently contains the data, you can use a package DBMS_DATAPUMP
to copy the entire schema. It is an interface to Datapump (as @Sylvain Leroux mentioned) that can be called from the database.
DECLARE
dph NUMBER;
source_schema VARCHAR2 (30) := 'SCHEMA_TO_EXPORT';
target_schema VARCHAR2 (30) := 'SCHEMA_TO_IMPORT';
job_name VARCHAR2 (30) := UPPER ('IMPORT_' || target_schema);
p_parallel NUMBER := 3;
v_start TIMESTAMP := SYSTIMESTAMP;
v_state VARCHAR2 (30);
BEGIN
dph :=
DBMS_DATAPUMP.open ('IMPORT',
'SCHEMA',
'DB_LINK_NAME',
job_name);
DBMS_OUTPUT.put_line ('dph = ' || dph);
DBMS_DATAPUMP.metadata_filter (dph,
'SCHEMA_LIST',
'''' || source_schema || '''');
DBMS_DATAPUMP.metadata_remap (dph,
'REMAP_SCHEMA',
source_schema,
target_schema);
DBMS_DATAPUMP.set_parameter (dph, 'TABLE_EXISTS_ACTION', 'REPLACE');
DBMS_DATAPUMP.set_parallel (dph, p_parallel);
DBMS_DATAPUMP.start_job (dph);
DBMS_DATAPUMP.wait_for_job (dph, v_state);
DBMS_OUTPUT.put_line ('Export/Import time: ' || (SYSTIMESTAMP - v_start));
DBMS_OUTPUT.put_line ('Final state: ' || v_state);
END;
The script actually copies and renames the schema. If you want to keep the same schema name, I believe you just remove the call metadata_remap
.
source to share
SQL Developer can help C # 1 by creating statements INSERT
with formatted query result :
Select /*insert*/ *
from My_Table;
source to share