| help with a query [message #560113] |
Tue, 10 July 2012 12:09  |
 |
BeefStu
Messages: 184
Registered: October 2011
|
Senior Member |
|
|
I am often taksed with refreshing schema's from one DB to anohter.
The first thing I need to check is the space the objects take up
in the source DB.
can somebody provide a SQL statement that prints the size of the following objects ('SEQUENCE','SYNONYM','TABLE','INDEX','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE') which is totaled by
owner, TS.. Or the SQL can just take in an owner.
I am looking for an output similiar to this:
[ocde]
TS name, owner, object_name, object_type MB SIZE
users xxx t1 table 100
users xxx t2 table 200
sum of tables 300
users xxx t1_idx index 10
sum of indexes 10
sum of tablespace 310
users1 xxx t1 table 10
users1 xxx t2 table 20
sum of tables 30
users1 xxx t1_idx index 5
sum of indexes 5
sum of tablespace 35
next TS
NEXT TS
....
[/code]
thanks to all who answer
[Updated on: Tue, 10 July 2012 12:14] Report message to a moderator |
|
|
|
|
|
| Re: help with a query [message #560116 is a reply to message #560113] |
Tue, 10 July 2012 12:18  |
 |
BlackSwan
Messages: 20242
Registered: January 2009
|
Senior Member |
|
|
>can somebody provide a SQL statement that prints the size of the following objects ('SEQUENCE','SYNONYM','TABLE','INDEX','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE')
mixing apples to bricks
('SEQUENCE','SYNONYM','PACKAGE','PACKAGE BODY','DATABASE LINK','VIEW','PROCEDURE')
above are stored as "metadata" within SYSTEM tablespace & only rarely consume any significant amount of space.
If close approximation is good enough for TABLE & INDEX, then do as below
SELECT ROUND(SUM(BYTES/(1024*1024))) MB FROM USER_TABLES;
SELECT ROUND(SUM(BYTES/(1024*1024))) MB FROM USER_INDEXES;
[Updated on: Tue, 10 July 2012 12:19] Report message to a moderator |
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
