| query in exporting only structure of tables [message #152809] |
Tue, 27 December 2005 01:53  |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
Hi,
I am exporting only structure of the one schema using followin command,
exp username/password file=/exp.dmp log=/explog.log data=n
now I am able to export the structure successfully.
Even I am able to import the file successfully but the space it occupy is almost same as it occupy with data.
why is it happening so?
Oracle version is 9.2.0.6
Or am I doing any mistake?
please guide me
Kinjal
|
|
|
|
|
|
| Re: query in exporting only structure of tables [message #152916 is a reply to message #152809] |
Tue, 27 December 2005 21:49  |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
hi,
I am really sorry.. I wrote data mistakenly..
The parameter is rows only..
I am really sorry again.
The command is,
exp username/password file=/exp.dmp log=/explog.log rows=n
And still the imported thing is cosuming almost same size..
What can be the problem?
Kinjal
|
|
|
|
| Re: query in exporting only structure of tables [message #152946 is a reply to message #152916] |
Wed, 28 December 2005 01:30 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
One possible reason I could think of , is that , there are no big tables in the schema! so whether u import them empty or with date, it may take same space..
to test this, try creating a big table e.g.
create table tst(a char(1000)) and insert into this table lots of rows say 20000 rows. and then try to export , hopefully , the 2 files will show differences now.
|
|
|
|
|
|
| Re: query in exporting only structure of tables [message #152950 is a reply to message #152947] |
Wed, 28 December 2005 01:45 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
That is very surprising..could you pl. post output of the following query:
connect to the SCHEMA that you are exporting, and being connected to that schema, pl. execute:
SQL> select sum(bytes) from user_segments;
might be there is something I do not know about, but I checked out on my environment, and there are very clear differences..
|
|
|
|
|
|
|
|
|
|
| Re: query in exporting only structure of tables [message #153079 is a reply to message #152809] |
Wed, 28 December 2005 22:27 |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
Hi,
I have attached the snapshot of my export command with this message.
The size of the dump file is 49236 bytes.
Kinjal
exp mudra/mudratest file=/bkup/test2tables.dmp log=/bkup/testlog.log tables=(CLIENTBILLDETAILTV,DEL33)
Connected to: Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table CLIENTBILLDETAILTV 72803 rows exported
. . exporting table DEL33 129653 rows exported
Export terminated successfully without warnings.
|
|
|
|
|
|
| Re: query in exporting only structure of tables [message #153100 is a reply to message #152809] |
Thu, 29 December 2005 01:19 |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
Hi,
I ran the same command for rows=n
and the size of dump file now is 76 bytes.
exp mudra/mudratest file=/bkup/test2tables.dmp log=/bkup/testlog.log tables=(CLIENTBILLDETAILTV,DEL33) rows=n
Export: Release 8.1.6.1.0 - Production on Thu Dec 29 12:41:37 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table CLIENTBILLDETAILTV
. . exporting table DEL33
Export terminated successfully without warnings.
kinjal.
|
|
|
|
|
|
|
|
| Re: query in exporting only structure of tables [message #153111 is a reply to message #153104] |
Thu, 29 December 2005 02:53 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
I think this is a clue:can u try to export with compress=n and then import and see ?
I just tried this and verified for one big table.
Pl. try first for one or two of VERY BIG tables and verify (by querying the size from user_segments view)
|
|
|
|
| Re: query in exporting only structure of tables [message #153196 is a reply to message #152809] |
Thu, 29 December 2005 22:33 |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
Hi,
I tried with all options like,
1. Exported with data and checked the number of bytes.
Imported with rows=n.
2. Exported with rows=n and imported with rows=n.
Number of bytes in both 1 and 2 was same.
3. Exproted with rows=n and compress=n.
Still after importing the bytes are same.
Isn't it strange?
What can be the problem?
Kinjal
|
|
|
|
| Re: query in exporting only structure of tables [message #153252 is a reply to message #153196] |
Fri, 30 December 2005 06:58 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
I am posting one test case..can u check whether u get the same results as I have:
SQL> create table ns_a(a char(1000));
Table created.
SQL> select sum(bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
65536 <<<<<<<<<<<<starting bytes in source
SQL> begin
2 for i in 1..500 loop
3 insert into ns_a values (to_char(i));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sum (bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
655360 <<<<<<<<<<<final bytes in source
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Dev1@mch1:/u01/app
$exp ns_cons1/tmp compress=y rows=n tables=(ns_a) file=ns_cons1.dmp log=ns_cons1.log
Export: Release 9.2.0.6.0 - Production on Fri Dec 30 17:14:49 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table NS_A
Export terminated successfully without warnings.
$imp fromuser=ns_cons1 touser=nschk2 file=ns_cons1.dmp
Import: Release 9.2.0.6.0 - Production on Fri Dec 30 17:19:14 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: /
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by NS_CONS1, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing NS_CONS1's objects into NSCHK2
Import terminated successfully without warnings.
Dev1@mch1:/u01/app
$sqlplus nschk2/tmp
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Dec 30 17:19:25 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
SQL> desc ns_a
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(1000)
SQL> select sum(bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
65536 <<<<<<<<<<<<<<<<<<<<bytes in target
(so even thought the bytes in source were 655360 in source, in the target they are 65536)
try to repeat same steps in ur environment and check IF U GET THE same last answer(sum(bytes)=65536 below...
Assuming that you do get the same results, then only last thing to check is perhaps the size of initial extent of ur tables that you are exporting..may be the INTIAL parameter of teh tables are too big and that's causing this but lets check that later..
|
|
|
|
| Re: query in exporting only structure of tables [message #153298 is a reply to message #152809] |
Fri, 30 December 2005 22:22 |
kinjal
Messages: 124
Registered: June 2001
|
Senior Member |
|
|
I have just copied the things as u said..
And you said the bytes in my case remains same,
It is as follows,
SQL> create table ns_a(a char(1000));
Table created.
SQL> select sum(bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
40960
SQL> begin
2 for i in 1..500 loop
3 insert into ns_a values(to_char(i));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select sum (bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
696320
SQL> exit
Disconnected from Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
[oracle@oratest oracle]$
[oracle@oratest oracle]$ exp mudra/mudratest compress=y rows=n file=/bkup/ns_test.dmp log=/bkup/ns_log.log tables=(ns_a)
Export: Release 8.1.6.1.0 - Production on Sat Dec 31 09:41:58 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table NS_A
Export terminated successfully without warnings.
imp kinjal/kinjal file=/bkup/ns_test.dmp fromuser=mudra touser=kinjal
Import: Release 8.1.6.1.0 - Production on Sat Dec 31 09:44:01 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
Warning: the objects were exported by MUDRA, not by you
import done in US7ASCII character set and US7ASCII NCHAR character set
Import terminated successfully without warnings.
sqlplus kinjal/kinjal
SQL*Plus: Release 8.1.6.0.0 - Production on Sat Dec 31 09:44:47 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected to:
Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production
SQL> desc ns_a;
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(1000)
SQL> select sum(bytes) from user_segments where segment_name='NS_A';
SUM(BYTES)
----------
696320
Now as u said there must be something related to first extent it is allocating..
How to solve this?
Kinjal
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
