Whilst writing up my last post it occurred to me that there is another useful feature of datapump that is pretty much ignored and this answers the age old question of 'how big is my table' quite well...
Now you may be reading this and thinking what's he on about i know how big my table is - i just look in USER_TABLES/USER_SEGMENTS/USER_EXTENTS and there it is (followed by what's this idiot on about)
but haha i say - are you sure you've really considered everything...?
Then you might get a little worried and think about it again but still confidently answer you just look in the dictionary views already mentioned,
What about if i say the word 'LOB' or the darkest of database evils 'nested tables' - still feeling confident now.... ? :-)
That's not fair you say - thats not normal stuff - no-one sensible uses those things.......
But they do, and more and more so (well lobs anyway) - so how to find how big stuff actually is?
Well you could try and write some really clever query that takes every conceivable thing you could do into account or....... you could let the developers who wrote datapump do the
So how do i get this sizing done for me? Well here is a quick example which shows for LOBS what the dictionary view shows versus what datapump shows, and a quick output from a nested table extract.
So first up we have a simple table where one of the columns is a lob - lets see how big the dictionary views think it is
SQL> desc sim_blob
Name Null? Type
----------------------------------------- -------- ----------------------------
SIM_RUN_ID NUMBER(38)
SIM_LENGTH NUMBER(38)
THE_BLOB BLOB
SQL> select bytes from user_segments where segment_name='SIM_BLOB'
2 /
BYTES
----------
196608
SQL>
So pretty small, lets now use datapump in estimate only mode to size it for us
[oracle@server]:dbname:/oracle/home/oracle# expdp user/pass tables=SIM_BLOB estimate_only=y
Export: Release 11.2.0.2.0 - Production on Tue Jul 1 23:02:41 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
Starting "SCHEMA"."SYS_EXPORT_TABLE_01": schema/******** tables=SIM_BLOB estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "SCHEMA"."SIM_BLOB" 3.349 MB
Total estimation using BLOCKS method: 3.349 MB
Job "SCHEMA"."SYS_EXPORT_TABLE_01" successfully completed at 23:02:59
And it's a lot bigger - as datapump included the space from the LOB object also
Now lets show the output if we extract a table that has a nested table object
[oracle@server]:dbname:[~]# expdp user/pass tables=trade_day estimate_only=y
Export: Release 11.2.0.2.0 - Production on Tue Jul 1 22:06:35 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option
Starting "SCHEMA"."SYS_EXPORT_TABLE_01": SCHEMA/******** tables=trade_day estimate_only=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "SCHEMA"."NTAB_TDY_TRADES" 20.95 GB
. estimated "SCHEMA"."TRADE_DAY" 1.299 GB
Total estimation using BLOCKS method: 22.25 GB
Job "SCHEMA"."SYS_EXPORT_TABLE_01" successfully completed at 22:07:14
And you can see it also includes the nested table sizing
All very useful - and it even gives you a sum of everything you specify at the end. It also does not need to create a dumpfile for an estimate_only extract.
Very useful i think as it takes into account every kind of weirdness you could think to include in the table design.
Comments
Post a Comment