| moving to new database after reducing table spaces [message #389004] |
Thu, 26 February 2009 23:14  |
karupps
Messages: 1
Registered: November 2008
Location: neyveli india
|
Junior Member |
|
|
Dear Sirs,
We are currently working on oracle database 7.0 and are in the process of migrating to oracle 10g R2. We are planning to export the contents from oracle 7.0 and import it in oracle 10g. we have already tested this in another server and succeeded. The real issue in this is that we have around 20 tablespaces in the oracle 7.0 and we want to reduce this to 5 or 6 in the proposed oracle 10g. I would like to know how this can be done.
Thanks in advance
K.Karuppasamy
|
|
|
|
|
|
|
|
|
|
| Re: moving to new database after reducing table spaces [message #389060 is a reply to message #389053] |
Fri, 27 February 2009 02:38  |
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
|
Senior Member |
 
|
|
SQL> select owner,tablespace_name from dba_segments where segment_name='EMP';
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
SYS SYSTEM
SCOTT USERS
SQL> host exp system/oracle file=d:\scott.dmp owner=scott;
Export: Release 10.2.0.1.0 - Production on Fri Feb 27 14:05:18 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
n
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table EMP 0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
SQL> create tablespace babu datafile 'D:\ORACLEXE\ORADATA\XE\BABU01.DBF' SIZE 10
0M;
Tablespace created.
SQL> create user babu identified by babu default tablespace babu quota unlimited
on babu;
User created.
SQL> grant connect to babu;
Grant succeeded.
SQL> host imp system/oracle file=d:\scott.dmp fromuser=scott touser=babu
Import: Release 10.2.0.1.0 - Production on Fri Feb 27 14:06:39 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
n
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into BABU
. . importing table "EMP" 0 rows imported
Import terminated successfully without warnings.
SQL> host imp system/oracle file=d:\scott.dmp fromuser=scott touser=babu show=y
Import: Release 10.2.0.1.0 - Production on Fri Feb 27 14:10:03 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio
n
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into BABU
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'XE', inst_scn=>'960936');"
"COMMIT; END;"
"[B]ALTER SESSION SET CURRENT_SCHEMA= "BABU[/B]""
"CREATE TABLE "EMP" ("A" NUMBER(*,0), "B" NUMBER(*,0)) PCTFREE 10 PCTUSED 4"
"0 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "EMP"
Import terminated successfully without warnings.
SQL> select owner,tablespace_name from dba_segments where segment_name='EMP';
OWNER TABLESPACE_NAME
------------------------------ ------------------------------
SYS SYSTEM
SCOTT USERS
BABU BABU
Mr Michel; Can you explain me the above code; I was thinking it's possible.
[Updated on: Fri, 27 February 2009 02:41] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
