OraFAQ Forum: Server Administration » To Monitor the increment of space of the tablespace based on the table

Home » RDBMS Server » Server Administration » To Monitor the increment of space of the tablespace based on the table (Oracle 10g , Linux)

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

To Monitor the increment of space of the tablespace based on the table [message #511005]

Thu, 09 June 2011 01:23

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

hi,

How to check for the increment of a space of the tablespace based on the particular table.
(i.e.)
Say a scenario, if am trying to load the data for a particular table , for first i loaded some 10000 records and then again loading 50000 records ,so based on the icrement of the reocrds the tablespace size also increases gradually . so for this scenario how to monitor the increment of the space.

let me know the suggestion for the same ,Thanks in advance.

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511006 is a reply to message #511005]

Thu, 09 June 2011 01:29

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Record the space before and after the load and see the difference.

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511007 is a reply to message #511006]

Thu, 09 June 2011 01:34

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

hi Michel,

you mean with the use of dba_free_space and dba_data_files.

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511010 is a reply to message #511007]

Thu, 09 June 2011 01:47

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, for instance.

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511015 is a reply to message #511010]

Thu, 09 June 2011 01:53

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Michel,

if you don't mind can you pls highly structure,this case

Thanks in advance,

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511016 is a reply to message #511015]

Thu, 09 June 2011 02:14

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't understand what you want from me?
Write the query to get the tablespace space?

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511019 is a reply to message #511016]

Thu, 09 June 2011 02:26

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

yes, if you don't mind .i am not Familiar with system views or tables.

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511021 is a reply to message #511019]

Thu, 09 June 2011 02:44

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on what you want exactly.

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511022 is a reply to message #511019]

Thu, 09 June 2011 02:48

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> desc dba_segments
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                                     VARCHAR2(30)
 SEGMENT_NAME                              VARCHAR2(81 CHAR)
 PARTITION_NAME                            VARCHAR2(30)
 SEGMENT_TYPE                              VARCHAR2(18 CHAR)
 TABLESPACE_NAME                           VARCHAR2(30)
 HEADER_FILE                               NUMBER
 HEADER_BLOCK                              NUMBER
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
 EXTENTS                                   NUMBER
 INITIAL_EXTENT                            NUMBER
 NEXT_EXTENT                               NUMBER
 MIN_EXTENTS                               NUMBER
 MAX_EXTENTS                               NUMBER
 PCT_INCREASE                              NUMBER
 FREELISTS                                 NUMBER
 FREELIST_GROUPS                           NUMBER
 RELATIVE_FNO                              NUMBER
 BUFFER_POOL                               VARCHAR2(7 CHAR)

Sum bytes per tablespace gives you the space used in each tablespace.

SQL> desc dba_free_space
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 TABLESPACE_NAME                           VARCHAR2(30)
 FILE_ID                                   NUMBER
 BLOCK_ID                                  NUMBER
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
 RELATIVE_FNO                              NUMBER

Sum bytes per tablespace gives you the space free in each tablespace.

SQL> desc dba_data_files
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 FILE_NAME                                 VARCHAR2(513)
 FILE_ID                                   NUMBER
 TABLESPACE_NAME                           VARCHAR2(30)
 BYTES                                     NUMBER
 BLOCKS                                    NUMBER
 STATUS                                    VARCHAR2(9 CHAR)
 RELATIVE_FNO                              NUMBER
 AUTOEXTENSIBLE                            VARCHAR2(3 CHAR)
 MAXBYTES                                  NUMBER
 MAXBLOCKS                                 NUMBER
 INCREMENT_BY                              NUMBER
 USER_BYTES                                NUMBER
 USER_BLOCKS                               NUMBER
 ONLINE_STATUS                             VARCHAR2(7 CHAR)

Sum bytes per tablespace gives you the space allocated to each tablespace.

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511029 is a reply to message #511022]

Thu, 09 June 2011 03:14

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

Michel,

Thanks for your information.

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511053 is a reply to message #511029]

Thu, 09 June 2011 04:43

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

hi MIchel,

Whether below query is correct for this case,if it's worng can u pls highlight it.

SELECT dfs.tablespace_name, ds.segment_name,
       SUM (ds.BYTES) / 1024 / 1024 AS used_mb,
       SUM (dfs.BYTES) / 1024 / 1024 AS free_mb,
       SUM (ddf.BYTES) / 1024 / 1024 AS allocated_mb
  FROM dba_segments ds, dba_free_space dfs, dba_data_files ddf
 WHERE ds.tablespace_name = dfs.tablespace_name AND dfs.file_id = ddf.file_id
   AND dfs.tablespace_name = <tblspac_name>
GROUP BY dfs.tablespace_name, ds.segment_name

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511054 is a reply to message #511053]

Thu, 09 June 2011 04:47

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Which result do you want from your query?

Regards
Michel

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511056 is a reply to message #511054]

Thu, 09 June 2011 04:56

ram anand
Messages: 244
Registered: February 2008
Location: india

Senior Member

For a particular segment name(say table XXXX) of the tablespace say 'XX001' need used,free,and allocated space for every 10000 records insert.

Report message to a moderator

Re: To Monitor the increment of space of the tablespace based on the table [message #511057 is a reply to message #511056]

Thu, 09 June 2011 05:06

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

For a particular segment name(say table XXXX) of the tablespace say 'XX001' need used,free,and allocated space

Use a subquery for each part:
- on dba_segments to get segment size
- on dba_data_files to get tablespace size
- on dba_free_space to get tablespace free size
(- the difference between the 2 previous gives the allocated space in the tablespace)
- join each subquery on tablespace name

Quote:

for every 10000 records insert.

No Oracle table/view will give you that.
You have to calculate it by yourself: size before, size after -> diff.

Regards
Michel

Report message to a moderator

Previous Topic:	tablespaces in autoextend settings
Next Topic:	MMNL absent for 1422 secs; Foregrounds taking over

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 06:26:31 CDT 2024