Home » RDBMS Server » Backup & Recovery » Check the DB Size exclude particular table "A" (Oracle 10)
Check the DB Size exclude particular table "A" [message #562711] Fri, 03 August 2012 12:09 Go to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Hi Friends,

I am trying to calculate the compressed size of backup of my database by excluding table space ( and all the tables in that table space).

I know how to calculate the size of particular user DB size.

select sum(bytes/1024/1024)"size" from dba_segments where
owner='ABC';

Can some one help me.

Regards
j
Re: Check the DB Size exclude particular table "A" [message #562712 is a reply to message #562711] Fri, 03 August 2012 12:15 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Do you mean a user managed backup, or a server managed (RMAN) backup? And what sort of compression?
Re: Check the DB Size exclude particular table "A" [message #562713 is a reply to message #562711] Fri, 03 August 2012 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
>I am trying to calculate the compressed size of backup of my database by excluding table space ( and all the tables in that table space).

I am unclear what you want to measure.
Exactly how did the data get compressed?
Upon what device does the compressed data reside?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Check the DB Size exclude particular table "A" [message #562716 is a reply to message #562713] Fri, 03 August 2012 14:42 Go to previous messageGo to next message
mjkreddy1927
Messages: 20
Registered: February 2007
Junior Member
Hi Swan,

What i mean to say is , i want to estimate the DB size for particular user, and i dont want to caluculate the size of particular table space ,because i dont want to take the backup of those tables.
Re: Check the DB Size exclude particular table "A" [message #562717 is a reply to message #562716] Fri, 03 August 2012 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57594
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your query is correct just add the segment is not the table you don't want.

Regards
Michel
Re: Check the DB Size exclude particular table "A" [message #562718 is a reply to message #562716] Fri, 03 August 2012 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC' and tablespace != '<excluded_TS>'
Re: Check the DB Size exclude particular table "A" [message #562719 is a reply to message #562718] Fri, 03 August 2012 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 57594
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think table space is in 2 words and so:

select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC' and segment_name != '<excluded table>';

Regards
Michel
Re: Check the DB Size exclude particular table "A" [message #562728 is a reply to message #562719] Fri, 03 August 2012 18:17 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Mjkreddy1927,

I find the following queries helpful for estimating the size of exports and imports.

The First query shows approximately how much space an export will consume.

The Second query shows approximately how much space an import will consume.

Alan
ECSCDAP1P > @meg_export

OWNER                        MEGABYTES
------------------------ -------------
ORDSYS                               0
OUTLN                                0
SCOTT                                0
CTXSYS                               1
EXFSYS                               1
DBSNMP                               1
WMSYS                                1
OLAPSYS                              3
ORDDATA                              7
SYSTEM                              17
SYSMAN                              19
APEX_030200                         44
ECSCDAP1P                           45
MDSYS                               55
ECSCDAP2P                           87
ECSCDAP4P                          101
ECSCDAP3P                          110
XDB                                112
SYS                               1800
CDA_PV_APPS                       4191
CDA_APPS                          4686
CDA_SHARED                        6232

ECSCDAP1P > list
  1  select owner,sum(bytes)/1024/1024 megabytes from dba_segments
  2  where segment_type not like 'INDEX%'
  3  and segment_type not like '%INDEX'
  4  group by owner
  5* order by megabytes

ECSCDAP1P > @meg_import

OWNER                        MEGABYTES
------------------------ -------------
SCOTT                                0
ORDSYS                               0
OUTLN                                1
DBSNMP                               2
WMSYS                                4
CTXSYS                               4
EXFSYS                               4
OLAPSYS                              5
ORDDATA                             14
SYSTEM                              34
ECSCDAP1P                           45
SYSMAN                              46
MDSYS                               74
APEX_030200                         86
ECSCDAP2P                           87
ECSCDAP4P                          101
ECSCDAP3P                          110
XDB                                158
SYS                               2779
CDA_PV_APPS                       5013
CDA_APPS                          5783
CDA_SHARED                        6335

ECSCDAP1P > list
  1  select owner,sum(bytes)/1024/1024 megabytes from dba_segments
  2  group by owner
  3* order by megabytes
Previous Topic: ORA-00310: archived log contains sequence 12; sequence 9 required :
Next Topic: Flash Recovery ->SYSTEM.DBF
Goto Forum:
  


Current Time: Wed Apr 16 01:09:15 CDT 2014

Total time taken to generate the page: 0.13217 seconds