Home » RDBMS Server » Server Administration » moving to new database after reducing table spaces (oracle 10g R2)
moving to new database after reducing table spaces [message #389004] Thu, 26 February 2009 23:14 Go to next message
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 #389005 is a reply to message #389004] Thu, 26 February 2009 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Pre-create objects in new tablespaces & then import with IGNORE=Y
Re: moving to new database after reducing table spaces [message #389049 is a reply to message #389005] Fri, 27 February 2009 02:15 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


In 10g;

1/ Create Tablespace in 10g (AS per your requirement)

2/ Assing Tablespace to Users

3/ Only Grant "Connnect" Role to Users

4/ Using System USer Try to import using your exported dump file.

Babu
Re: moving to new database after reducing table spaces [message #389053 is a reply to message #389049] Fri, 27 February 2009 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What if objects of a user should be spread over 2 or 3 tablespaces? You can't do it this way.
BlackSwan gave the correct answer.

Regards
Michel

[Updated on: Fri, 27 February 2009 03:18]

Report message to a moderator

Re: moving to new database after reducing table spaces [message #389060 is a reply to message #389053] Fri, 27 February 2009 02:38 Go to previous message
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

Previous Topic: Alert log error
Next Topic: "ORA-27102: out of memory" and SGA
Goto Forum:
  


Current Time: Tue Dec 03 14:25:14 CST 2024