OraFAQ Forum: Server Utilities » Import user Without Data

Home » RDBMS Server » Server Utilities » Import user Without Data (Oracle 10G r2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import user Without Data [message #630867]

Mon, 05 January 2015 23:15

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

Report message to a moderator

Re: Import user Without Data [message #630884 is a reply to message #630867]

Tue, 06 January 2015 01:17

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Import user Without Data [message #630888 is a reply to message #630884]

Tue, 06 January 2015 01:54

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

Report message to a moderator

Re: Import user Without Data [message #630889 is a reply to message #630888]

Tue, 06 January 2015 01:56

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;

Report message to a moderator

Re: Import user Without Data [message #630891 is a reply to message #630889]

Tue, 06 January 2015 02:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Import user Without Data [message #630900 is a reply to message #630891]

Tue, 06 January 2015 03:24

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

Report message to a moderator

Re: Import user Without Data [message #630902 is a reply to message #630900]

Tue, 06 January 2015 03:28

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

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

The syntax is wrong, fix it and retry.

Report message to a moderator

Re: Import user Without Data [message #630905 is a reply to message #630902]

Tue, 06 January 2015 04:18

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

Report message to a moderator

Re: Import user Without Data [message #630908 is a reply to message #630905]

Tue, 06 January 2015 04:31

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Import user Without Data [message #630910 is a reply to message #630908]

Tue, 06 January 2015 04:41

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

Report message to a moderator

Re: Import user Without Data [message #630911 is a reply to message #630910]

Tue, 06 January 2015 04:46

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: Import user Without Data [message #630927 is a reply to message #630911]

Tue, 06 January 2015 06:09

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

Report message to a moderator

Re: Import user Without Data [message #630929 is a reply to message #630927]

Tue, 06 January 2015 06:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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.)

Report message to a moderator

Re: Import user Without Data [message #630931 is a reply to message #630929]

Tue, 06 January 2015 06:26

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

Report message to a moderator

Re: Import user Without Data [message #630932 is a reply to message #630931]

Tue, 06 January 2015 06:28

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I laready answered this question.
Where is the problem?

Report message to a moderator

Re: Import user Without Data [message #630933 is a reply to message #630932]

Tue, 06 January 2015 06:45

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

Report message to a moderator

Re: Import user Without Data [message #630939 is a reply to message #630933]

Tue, 06 January 2015 08:00

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

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

Yes and indexes and partitions.

Report message to a moderator

Re: Import user Without Data [message #630961 is a reply to message #630939]

Tue, 06 January 2015 23:46

mmohsinaziz
Messages: 110
Registered: May 2012

Senior Member

Thanks Michel.
It resolves the problem.

Report message to a moderator

Previous Topic:	Export Problems Oracle 10g
Next Topic:	How to export using datapump excluding mutiple table in windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:53:59 CST 2025