 Copy part of database to a new database [message #326384] |
Wed, 11 June 2008 05:21  |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
