Home » RDBMS Server » Server Utilities » Dump size is more that size available in dba_data_files (11g, 11.2.0.3, Window 7)
Dump size is more that size available in dba_data_files [message #628149] Thu, 20 November 2014 08:20 Go to next message
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 #628157 is a reply to message #628149] Thu, 20 November 2014 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select round(sum(bytes)/1024/1024) from dba_segments where owner='TEST';

[Updated on: Thu, 20 November 2014 09:22]

Report message to a moderator

Re: Dump size is more that size available in dba_data_files [message #628207 is a reply to message #628157] Fri, 21 November 2014 04:21 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Thanks.

You are correct.

SQL> select round(sum(bytes)/1024/1024) from dba_segments where owner='TEST';

ROUND(SUM(BYTES)/1024/1024)
---------------------------
                        237


I checked it and found compressed and UNCOMPRESSED dump is lesser than dba_data_files.

Regards,
Ashish
Re: Dump size is more that size available in dba_data_files [message #628209 is a reply to message #628149] Fri, 21 November 2014 04:42 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
One slight quibble now that Michel has given you your answer, don't grant the DBA role to users. I know it is only a test but it is a bad habit to get into.
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 Go to previous message
Michel Cadot
Messages: 66437
Registered: March 2007
Location: Nanterre, France, http://...
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;

Previous Topic: Problem loading data using SQL Loader
Next Topic: Multiple "Include" in impdp
Goto Forum:
  


Current Time: Sun Jun 16 19:37:57 CDT 2019