How to change the default tablespace when importing using the INDEXFILE option
Doc ID: Note:1068183.6 Type: PROBLEM
Last Revision Date: 18-JAN-2004 Status: PUBLISHED
Problem Description:
====================
You are importing a table and receive the following error:
ORA-01658: unable to create INITIAL extent for segment in tablespace %s
Cause: Failed to find sufficient contiguous space to allocate INITIAL
extent for segment being created.
Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
tablespace or retry with a smaller value for INITIAL
You realize that you do not have enough space in the tablespace for the table,
but do not want to add a datafile to that tablespace. So, you need to find out
how to change the default tablespace when importing.
Problem Explanation:
====================
By default an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER, so you have to force the import utility into
placing the objects into another tablespace.
Problem References:
===================
Note 61949.1 Export and Import Bulletin
Note 1012307.6 Moving Tables Between Tablespaces Using Export/Import
Solution Description:
=====================
You need to use the INDEXFILE option, edit the file to change the default
tablespace, use script to precreate the table in the new tablespace, and then
do the import.
EXAMPLE TEST TO CHANGE DEFAULT TABLESPACE USING INDEXFILE OPTION:
================================================================
1) Create user REPORTS with default tablespace of TOOLS.
Oracle Server Manager Release 2.3.4.0.0 - Production
Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.
Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
SVRMGR> connect internal
Connected.
SVRMGR> CREATE USER reports IDENTIFIED BY reports;
Statement processed.
SVRMGR> GRANT DBA TO reports;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO reports;
Statement processed.
SVRMGR> ALTER USER reports IDENTIFIED BY reports DEFAULT TABLESPACE tools;
Statement processed.
SVRMGR> ALTER USER reports QUOTA UNLIMITED ON tools;
Statement processed.
SVRMGR> CONNECT reports/reports;
Connected.
SVRMGR> CREATE TABLE test_table AS SELECT * FROM all_objects;
Statement processed.
SVRMGR> SELECT owner, table_name, tablespace_name FROM DBA_TABLES where owner =
'REPORTS';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
REPORTS TEST_TABLE TOOLS
1 row selected.
2) Do export of TEST_TABLE.
[otchp1]/u02/home/usupport> exp reports/reports tables="(test_table)" file=
sadavid.dmp log=exp1.log
Export: Release 7.3.4.1.0 - Production on Wed Feb 24 19:39:24 1999
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export done in US7ASCII character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_TABLE 1891 rows exported
Export terminated successfully without warnings.
3) Create user SADAVID with default tablespace of USERS.
SVRMGR> CREATE USER sadavid IDENTIFIED BY sadavid;
Statement processed.
SVRMGR> GRANT DBA TO sadavid;
Statement processed.
SVRMGR> GRANT UNLIMITED TABLESPACE TO sadavid;
Statement processed.
SVRMGR> ALTER USER sadavid IDENTIFIED BY sadavid DEFAULT TABLESPACE users;
Statement processed.
SVRMGR> ALTER USER sadavid QUOTA UNLIMITED ON users;
Statement processed.
4) Do import as SADAVID. Note that the default tablespace does not change.
[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)"
Import: Release 7.3.4.1.0 - Production on Wed Feb 24 19:44:08 1999
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export file created by EXPORT:V07.03.04 via conventional path
Warning: the objects were exported by REPORTS, not by you
. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.
5) NOTE: The table was created in TOOLS tablespace instead of USERS
tablespace.
SVRMGR> select owner, table_name, tablespace_name from dba_tables
2> where owner = 'SADAVID';
OWNER TABLE_NAMETABLESPACE_NAME
------------------------------ ------------------------------
SADAVID TEST_TABLE TOOLS
1 row selected.
SVRMGR> connect sadavid/sadavid
Connected.
6) Do import using INDEXFILE option.
[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" INDEXFILE=indexfile.log
Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:04:39 1999
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export file created by EXPORT:V07.03.04 via conventional path
Warning: the objects were exported by REPORTS, not by you
. . skipping table "TEST_TABLE"
Import terminated successfully without warnings.
7) Edit "indexfile.sql", log in as sadavid, and run script to precreate the
table.
CREATE TABLE "SADAVID"."TEST_TABLE" ("OWNER" VARCHAR2(30) NOT NULL,
"OBJECT_NAME" VARCHAR2(30) NOT NULL, "OBJECT_ID" NUMBER NOT NULL,
"OBJECT_TYPE" VARCHAR2(12), "CREATED" DATE NOT NULL, "LAST_DDL_TIME"
DATE NOT NULL, "TIMESTAMP" VARCHAR2(75), "STATUS" VARCHAR2(7))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 266240
NEXT 126976 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "USERS" ;
SVRMGR> connect sadavid/sadavid
Connected.
SVRMGR> drop table test_table;
Statement processed.
SVRMGR> @indexfile.sql
Statement processed.
8)Run import with IGNORE=Y to populate the new table:
[otchp1]/u02/home/usupport> imp sadavid/sadavid file=sadavid.dmp tables=
"(test_table)" ignore=y
Import: Release 7.3.4.1.0 - Production on Wed Feb 24 20:12:42 1999
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to: Oracle7 Server Release 7.3.4.1.0 - Production
With the distributed, replication, parallel query and Spatial Data options
PL/SQL Release 2.3.4.1.0 - Production
Export file created by EXPORT:V07.03.04 via conventional path
Warning: the objects were exported by REPORTS, not by you
. importing REPORTS's objects into SADAVID
. . importing table "TEST_TABLE" 1891 rows imported
Import terminated successfully without warnings.
9) NOTE TEST_TABLE HAS A NEW TABLESPACE OF USERS:
SVRMGR> select owner, table_name, tablespace_name from dba_tables where owner =
'SADAVID';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SADAVID TEST_TABLE USERS
1 row selected.
Solution Explanation:
=====================
By default, an import will try to import tables into the same tablespace they
were exported from. There is not an equivalent FROM_TABLESPACE/TO_TABLESPACE
similar to FROMUSER/TOUSER. So you have to force the import utility into
placing the objects into another tablespace.
No comments:
Post a Comment