Dump size is more that size available in dba_data_files [message #628149] |
Thu, 20 November 2014 08:20  |
ashishkumarmahanta80
Messages: 231 Registered: October 2006 Location: kolkatta
|
Senior Member |
|
|
Dear All,
I have created new user TEST and associated it to newly created tablespace TEST.
create tablespace test datafile 'E:\APP\ORADATA\ASHTEST\TEST01.dbf' size 100m autoextend on next 10m;
create user test identified by test
DEFAULT tablespace test
temporary tablespace temp
quota unlimited on test;
grant dba to test;
SQL> select file_name, bytes/1024/1024 bytes_mb from dba_data_files
2 where tablespace_name ='TEST';
FILE_NAME BYTES_MB
-------------------------------------------------- ----------
E:\APP\ORADATA\ASHTEST\TEST01.DBF 100
Here, total bytes is around 100MB. And few tables imported into it. When I do export by using parameter compression (with & without). Dump size is appearing as 180MB(without compression) and 175 MB (compression).
Export: Release 11.2.0.1.0 - Production on Thu Nov 20 19:02:52 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=expdp_TEST.dmp logfile=expdp_TEST.log directory=dp_dir schemas=TEST compression=none
Estimate in progress using BLOCKS method...
Export: Release 11.2.0.1.0 - Production on Thu Nov 20 19:32:06 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=expdp_TEST_without_compression.dmp logfile=expdp_TEST_without_compression.log directory=dp_dir schemas=TEST
Please assist me to understand the logic behind it. Why it is more in dump than dba_data_files.
Regards,
Ashish
|
|
|
|
|
|
Re: Dump size is more that size available in dba_data_files [message #628242 is a reply to message #628207] |
Fri, 21 November 2014 10:19  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote:I checked it and found compressed and UNCOMPRESSED dump is lesser than dba_data_files.
Thanks for the feedback.
Also check if account owns objects in other tablespaces (as you granted it DBA role).
select tablespace_name, round(sum(bytes)/1024/1024)
from dba_segments
where owner='TEST'
group by tablespace_name;
|
|
|