OraFAQ Forum: Backup & Recovery » Check the DB Size exclude particular table "A"

Home » RDBMS Server » Backup & Recovery » Check the DB Size exclude particular table "A" (Oracle 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Check the DB Size exclude particular table "A" [message #562711]

Fri, 03 August 2012 12:09

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

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562712 is a reply to message #562711]

Fri, 03 August 2012 12:15

John Watson
Messages: 8962
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?

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562713 is a reply to message #562711]

Fri, 03 August 2012 12:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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/

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562716 is a reply to message #562713]

Fri, 03 August 2012 14:42

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.

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562717 is a reply to message #562716]

Fri, 03 August 2012 14:49

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So your query is correct just add the segment is not the table you don't want.

Regards
Michel

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562718 is a reply to message #562716]

Fri, 03 August 2012 14:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

select sum(bytes/1024/1024)"size" from dba_segments where owner='ABC' and tablespace != '<excluded_TS>'

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562719 is a reply to message #562718]

Fri, 03 August 2012 15:00

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Check the DB Size exclude particular table "A" [message #562728 is a reply to message #562719]

Fri, 03 August 2012 18:17

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

Report message to a moderator

Previous Topic:	ORA-00310: archived log contains sequence 12; sequence 9 required :
Next Topic:	Flash Recovery ->SYSTEM.DBF

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:17:12 CST 2024