Home » SQL & PL/SQL » SQL & PL/SQL » help with a query
help with a query [message #560113] Tue, 10 July 2012 12:09 Go to next message
BeefStu
Messages: 192
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 #560115 is a reply to message #560113] Tue, 10 July 2012 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I often asked to use Preview button BEFORE to check if the post is correctly formatted.

Regards
Michel
Re: help with a query [message #560116 is a reply to message #560113] Tue, 10 July 2012 12:18 Go to previous messageGo to next message
BlackSwan
Messages: 22718
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

Re: help with a query [message #560119 is a reply to message #560113] Tue, 10 July 2012 12:25 Go to previous message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Things that use space are segments.
Segment size can be found in dba_segments.

Regards
Michel
Previous Topic: Wrong value
Next Topic: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Goto Forum:
  


Current Time: Sat Aug 30 07:56:57 CDT 2014

Total time taken to generate the page: 0.12875 seconds