Home » RDBMS Server » Server Utilities » Import user Without Data (Oracle 10G r2)
Import user Without Data [message #630867] Mon, 05 January 2015 23:15 Go to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Dear Seniors,
I have exported the user without data using the following command.
       > expdp user/passsword dumpfile=exp_no_data.dmp directory=dmpdir schemas=user1 content=metadata_only logfile=user.log



I have created a new tablespace and user to import the tables in new user using the following command.


       > impdp user/password remap_schema=user1:user2 directory=dmpdir dumpfile=exp_no_data.DMP remap_tablespace = user1:user2


The tablespace of exported user is 2GB and consumed tablesspace is 1500MB. The new user 'USER2' tablespace is 500MB as i only want the import the tables structures, constraints, views etc but no data. But when I import the data using the above command, it stop the processing, waiting for a while I increase the tablespace to 1500MB and process started again and finished.
There was no data in the tables but tablespace is 99% used.

Please guide and advice me how to use correct command line pamameters to get rid of this problem. In toad schema browser in shows numbers rows in 'Num Rows' columns but there are no rows in the table.

Please help to resolve.
Thanks
Re: Import user Without Data [message #630884 is a reply to message #630867] Tue, 06 January 2015 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First, what is the command you used to create the tablespace?

Re: Import user Without Data [message #630888 is a reply to message #630884] Tue, 06 January 2015 01:54 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Dear Michel,
Following command was used to create table space.

SQL>CREATE TABLESPACE USER2 DATAFILE 
   'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PARACHA1314.ORA' SIZE 500M AUTOEXTEND OFF
    LOGGING
    PERMANENT
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    BLOCKSIZE 8K
    SEGMENT SPACE MANAGEMENT MANUAL
    FLASHBACK ON;



Regards
Re: Import user Without Data [message #630889 is a reply to message #630888] Tue, 06 January 2015 01:56 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Please discard my previous reply.
SQL>CREATE TABLESPACE USER2 DATAFILE 
   'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\user2.ORA' SIZE 500M AUTOEXTEND OFF
    LOGGING
    PERMANENT
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    BLOCKSIZE 8K
    SEGMENT SPACE MANAGEMENT MANUAL
    FLASHBACK ON;
Re: Import user Without Data [message #630891 is a reply to message #630889] Tue, 06 January 2015 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can try using the TRANSFORM parameter, something like: "TRANSFORM=STORAGE:n".

If this does not work, import using SQLFILE parameter to get the DDL of the objects, modify the storage parameters and execute the file.

Re: Import user Without Data [message #630900 is a reply to message #630891] Tue, 06 January 2015 03:24 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Dear Michel,
I have used the following command but it does not work.
 > impdp user/password remap_schema=user1:user2 directory=dmpdir dumpfile=exp_no_data.DMP remap_tablespace = user1:user2 transformSTORAGE:n


please guide me how to create and use SQLFILE in impdp command. what storage parameters i have to change and what will be new value.

Thanks
Re: Import user Without Data [message #630902 is a reply to message #630900] Tue, 06 January 2015 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have used the following command but it does not work.


The syntax is wrong, fix it and retry.

Re: Import user Without Data [message #630905 is a reply to message #630902] Tue, 06 January 2015 04:18 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
corrected the syntax and command runs but it used the tablespace same as previous.
> impdp user/password remap_schema=user1:user2 directory=dmpdir dumpfile=exp_no_data.DMP remap_tablespace = user1:user2 transform=STORAGE:n


SQLFiLE is created. Please help how to alter storage parameter in SQLFILE.

Thanks
Re: Import user Without Data [message #630908 is a reply to message #630905] Tue, 06 January 2015 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just use your favorite text editor and force the table to use only one small extend.

Re: Import user Without Data [message #630910 is a reply to message #630908] Tue, 06 January 2015 04:41 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Dear Michel,
Following is the part of create table portion of SQLFILE. What to modify to force the to use small extend only.
CREATE TABLE "USER2"."ADVANCE" 
   (	"EMPID" VARCHAR2(7), 
	"ADVDATE" DATE, 
	"DEPTID" VARCHAR2(2), 
	"AMOUNT" NUMBER(7,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USER2" ;

Please modify it. Thanks
Re: Import user Without Data [message #630911 is a reply to message #630910] Tue, 06 January 2015 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Modify INITIAL to set it to your block size (minimum possible) and ensure that all MINEXTENTS is set to 1.
How many segments (tables, partitions, indexes...) have you?


Re: Import user Without Data [message #630927 is a reply to message #630911] Tue, 06 January 2015 06:09 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Following is the required detail.
tables = 442
views = 444
partitions = 1
indexes = 305
triggers = 15
procedures =5
functions = 9
constraints = 663
sequences = 3
types = 2
Directories = 10

Block size = 8K
Re: Import user Without Data [message #630929 is a reply to message #630927] Tue, 06 January 2015 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With these figures, at least (442+1+305)*64K+overhead = about 50MB woill be needed.
(64K is the minimum extent size in AUTOALLOCATE tablespace.)

Re: Import user Without Data [message #630931 is a reply to message #630929] Tue, 06 January 2015 06:26 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Please help how to fit this in following create table statement.
CREATE TABLE "USER2"."ADVANCE" 
   (	"EMPID" VARCHAR2(7), 
	"ADVDATE" DATE, 
	"DEPTID" VARCHAR2(2), 
	"AMOUNT" NUMBER(7,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USER2" ;


Thanks
Re: Import user Without Data [message #630932 is a reply to message #630931] Tue, 06 January 2015 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I laready answered this question.
Where is the problem?

Re: Import user Without Data [message #630933 is a reply to message #630932] Tue, 06 January 2015 06:45 Go to previous messageGo to next message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Dear Michel,
I have modified the Storage parameters as per my understanding.Please advice should i modify the same storage parameter in SQLFILE for all the tables.
CREATE TABLE "USER2"."ADVANCE" 
   (	"EMPID" VARCHAR2(7), 
	"ADVDATE" DATE, 
	"DEPTID" VARCHAR2(2), 
	"AMOUNT" NUMBER(7,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 52428800
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USER2" ;

Also, please correct the stetement if i have not properly changed the storage parameter as you instructed.

Thanks
Re: Import user Without Data [message #630939 is a reply to message #630933] Tue, 06 January 2015 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66563
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
should i modify the same storage parameter in SQLFILE for all the tables.


Yes and indexes and partitions.

Re: Import user Without Data [message #630961 is a reply to message #630939] Tue, 06 January 2015 23:46 Go to previous message
mmohsinaziz
Messages: 110
Registered: May 2012
Senior Member
Thanks Michel.
It resolves the problem.
Previous Topic: Export Problems Oracle 10g
Next Topic: How to export using datapump excluding mutiple table in windows
Goto Forum:
  


Current Time: Sun Sep 22 02:54:08 CDT 2019