Home » RDBMS Server » Backup & Recovery » Copy part of database to a new database (Oracle 9i, Solaris)
icon9.gif  Copy part of database to a new database [message #326384] Wed, 11 June 2008 05:21 Go to next message
mshravan_020
Messages: 4
Registered: June 2008
Junior Member
Hello Group,

I am a basic Oracle SQL user. I have been working on a task where-in we have a database which is functional, has lot of tables and lots of data in them.

We would like to create a copy of this database such that we have all the objects[tables, views, indexes, constraints, stored procedures and so on] definitions available in the new database, and the conditional data for few tables and whole data for few other tables.

In order to achieve the above requirement, I have been advised by one of our oracle dba's here to use Oracle's Export/Import feature. Therefore, I have explored this aspect and gathered some information to initiate this task. I have stepped through http://www.orafaq.com/wiki/Import_Export_FAQ site, where very good information required to achieve my above task has been documented. But, unfortunately I was not able to achieve this task.

Below is what I have done so far in order to achieve this task:

I have exported definitions[I mean I have exported only table structures and not data] from database and tried to import.

Import says, imported successfully with some warnings. Now when I login as training_new user there are no table definitions created there, i.e., when I run "select * from tab", Oracle reports "no rows selected".

Please advise, what could be the reason for not able to import as assumed and hele me achieve this task as soon as possible.

--------------------------------------------------------------

Overview
========
1) Create a tablespace for new database.
2) Export blank database and specific data from different tables.
3) Import this exported data to new database.

Preface:
~~~~~~~~
Old Username: training
Old Tablespace: JUICY_DATA_01

New Username: training_new
New Tablespace: training08

Oracle Version: Oracle 9i

Details:
========
1) Create a tablespace for new database:

CREATE TABLESPACE training08 DATAFILE '/oracle/app/oradata/testdb/juicy_training08_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

---------------------------------------------

2) Export blank database and specific data from different tables.
NOTE: When executing below "EXP..." script in client database, make sure to change username/pwd, LOG and FILE locations.

exp training/training@testdb BUFFER=2000000 DIRECT=Y LOG=/home/oracle/msk_dumps/training_exp01.log FILE=/home/oracle/msk_dumps/training_exp01.dmp FILESIZE=536832000 RECORDLENGTH=64000 ROWS=N STATISTICS=NONE TABLESPACES=SOLAR_DATA_01

---------------------------

3) Import this exported data[just data structure] to new database.
3.1) Create a new user trainingva_new for trainingva08 tablespace and grant all required privileges.
SQL> CREATE USER training_new IDENTIFIED BY training_new DEFAULT TABLESPACE training08 QUOTA UNLIMITED ON training08;

SQL> grant create session, grant any privilege to training_new;

SQL> grant all privileges to training_new;

3.2) Change 'training_new' user quota to new tablespace.
SQL> alter user training_new quota 0 on JUICY_DATA_01 quota unlimited on training08;
SQL> alter user training quota 0 on JUICY_DATA_01 quota unlimited on training08;

3.3) REVOKE unlimited tablespace from training_new user.
SQL> revoke unlimited tablespace from training_new;
SQL> revoke unlimited tablespace from training;

3.4) Change default tablespace of exported user to new tablespace.
SQL> alter user training default tablespace training08;

3.5) grant dba to training_new;

3.6) Import exported data to trainingva08 tablespace.
Command prompt>
imp training_new/training_new@testdb FROMUSER=training TOUSER=training_new FILE=/home/oracle/msk_dumps/training_exp01.dmp LOG=/home/oracle/msk_dumps/training08_imp_log.log FILESIZE=536832000 INDEXES=N INDEXFILE=/home/oracle/msk_dumps/training_indexes.sql ROWS=N STATISTICS=NONE IGNORE=N TABLESPACES=training08

4) revoke dba from training_new;

5) grant unlimited tablespace to training_new;

---------------------------------------------

4) Export data incrementally.
4.1) Export & Import WHOLE data from below tables.
TABLES: <TODO>
4.2) Export & Import CONDITIONAL data from below tables.
TABLES: <TODO>

--------------------------------------------------------------

Please let me know if you need any other information, to assist me.

Thanks in advance,
Shravan
Re: Copy part of database to a new database [message #326394 is a reply to message #326384] Wed, 11 June 2008 06:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Always use code tags for the code.
Always please post the code and its output (or atleast the relevant part of it).
Explaining what you did will help us less.

>> INDEXFILE=/home/oracle/msk_dumps/training_indexes.sql
You are using Indexfile option, which will not actually import anything.
Instead it will just dump all the ddl to the mentioned log file.
Re: Copy part of database to a new database [message #326437 is a reply to message #326394] Wed, 11 June 2008 09:03 Go to previous message
mshravan_020
Messages: 4
Registered: June 2008
Junior Member
Thanks for your valuable reply Mahesh.

Your help solved my issue.
Previous Topic: clearing unarchived log file
Next Topic: re-store offline backup
Goto Forum:
  


Current Time: Mon Dec 05 04:42:47 CST 2016

Total time taken to generate the page: 0.10130 seconds