OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » SYSAUX tablespace (linux,windows)

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

SYSAUX tablespace [message #537664]

Fri, 30 December 2011 02:28

sandeip
Messages: 23
Registered: May 2011
Location: pune

Junior Member

Hi,

Please help,

Used space of sysaux tablespace of my production database decreases instead of increase.
To moniter the same i use following query

--Tablespace avail,used,free space--
SELECT
dts.tablespace_name,
(ddf.bytes / 1024 / 1024) "AvailSpace",
(ddf.bytes - (dfs.bytes))/1024/1024 "UsedSpace",
(dfs.bytes / 1024/1024 ) "FreeSpace",
TO_CHAR(((ddf.bytes - (dfs.bytes)) / ddf.bytes * 100),'990.00') "Used %"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY')
UNION ALL
SELECT dts.tablespace_name,
(dtf.bytes / 1024 / 1024) "AvailSpace",
(t.bytes)/1024/1024 "UsedSpace",
(dtf.bytes - (t.bytes))/1024/1024 "FreeSpace",
TO_CHAR((t.bytes / dtf.bytes * 100), '990.00') "Used %"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files group by tablespace_name) dtf,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_space_header group by tablespace_name) t
WHERE
dts.tablespace_name = dtf.tablespace_name(+)
AND dts.tablespace_name = t.tablespace_name(+)
AND dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY'

and save the result of same daily.

Please help me to troubleshoot from the same.

Report message to a moderator

Re: SYSAUX tablespace [message #537681 is a reply to message #537664]

Fri, 30 December 2011 03:39

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

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: SYSAUX tablespace [message #537686 is a reply to message #537681]

Fri, 30 December 2011 04:07

sandeip
Messages: 23
Registered: May 2011
Location: pune

Junior Member

Details are as below
database version: 11.2.0.1.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2

From last few days i found that used space of SYSAUX tablespace goes on decreasing instead of increase.

Tell why this happens(normally used space of tablespace increses but here its decreases)
Whether this is normal..? or something is wrong happens in my database.

Report message to a moderator

Re: SYSAUX tablespace [message #537689 is a reply to message #537686]

Fri, 30 December 2011 04:11

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

Senior Member
Account Moderator

How did you manage that used space changed?

Regards
Michel

Report message to a moderator

Re: SYSAUX tablespace [message #537690 is a reply to message #537689]

Fri, 30 December 2011 04:38

sandeip
Messages: 23
Registered: May 2011
Location: pune

Junior Member

Hi,
I used following query to moniter the space of tablespace ==>

SELECT
dts.tablespace_name,
(ddf.bytes / 1024 / 1024) "AvailSpace",
(ddf.bytes - (dfs.bytes))/1024/1024 "UsedSpace",
(dfs.bytes / 1024/1024 ) "FreeSpace",
TO_CHAR(((ddf.bytes - (dfs.bytes)) / ddf.bytes * 100),'990.00') "Used %"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
AND NOT (dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY')
UNION ALL
SELECT dts.tablespace_name,
(dtf.bytes / 1024 / 1024) "AvailSpace",
(t.bytes)/1024/1024 "UsedSpace",
(dtf.bytes - (t.bytes))/1024/1024 "FreeSpace",
TO_CHAR((t.bytes / dtf.bytes * 100), '990.00') "Used %"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files group by tablespace_name) dtf,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_space_header group by tablespace_name) t
WHERE
dts.tablespace_name = dtf.tablespace_name(+)
AND dts.tablespace_name = t.tablespace_name(+)
AND dts.extent_management like 'LOCAL'
AND dts.contents like 'TEMPORARY'

Daily,i used to save the output of given query.
Please find the attachment for same,in which used space goes on decreasing.

Attachment: SYSAUX_20111228.txt
(Size: 1.30KB, Downloaded 1094 times)

Report message to a moderator

Re: SYSAUX tablespace [message #537697 is a reply to message #537690]

Fri, 30 December 2011 05:11

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

Senior Member
Account Moderator

I can't read not formatted query.

Regards
Michel

Report message to a moderator

Re: SYSAUX tablespace [message #537702 is a reply to message #537690]

Fri, 30 December 2011 05:39

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

@ Michel,

SELECT dts.tablespace_name, 
       ( ddf.bytes / 1024 / 1024 ) 
       "AvailSpace", 
       ( ddf.bytes - ( dfs.bytes ) ) / 1024 / 1024 
       "UsedSpace", 
       ( dfs.bytes / 1024 / 1024 ) 
       "FreeSpace", 
       To_char(( ( ddf.bytes - ( dfs.bytes ) ) / ddf.bytes * 100 ), '990.00') 
       "Used %" 
FROM   sys.dba_tablespaces dts, 
       (SELECT tablespace_name, 
               SUM(bytes) bytes 
        FROM   dba_data_files 
        GROUP  BY tablespace_name) ddf, 
       (SELECT tablespace_name, 
               SUM(bytes) bytes 
        FROM   dba_free_space 
        GROUP  BY tablespace_name) dfs 
WHERE  dts.tablespace_name = ddf.tablespace_name(+) 
       AND dts.tablespace_name = dfs.tablespace_name(+) 
       AND NOT ( dts.extent_management LIKE 'LOCAL' 
                 AND dts.contents LIKE 'TEMPORARY' ) 
UNION ALL 
SELECT dts.tablespace_name, 
       ( dtf.bytes / 1024 / 1024 )                      "AvailSpace", 
       ( t.bytes ) / 1024 / 1024                        "UsedSpace", 
       ( dtf.bytes - ( t.bytes ) ) / 1024 / 1024        "FreeSpace", 
       To_char(( t.bytes / dtf.bytes * 100 ), '990.00') "Used %" 
FROM   sys.dba_tablespaces dts, 
       (SELECT tablespace_name, 
               SUM(bytes) bytes 
        FROM   dba_temp_files 
        GROUP  BY tablespace_name) dtf, 
       (SELECT tablespace_name, 
               SUM(bytes_used) bytes 
        FROM   v$temp_space_header 
        GROUP  BY tablespace_name) t 
WHERE  dts.tablespace_name = dtf.tablespace_name(+) 
       AND dts.tablespace_name = t.tablespace_name(+) 
       AND dts.extent_management LIKE 'LOCAL' 
       AND dts.contents LIKE 'TEMPORARY'

@ OP
Any purge on sysaux occupants ?
cleared any logs ?or AWR data ? etc .....any clue ?

Sriram

Report message to a moderator

Re: SYSAUX tablespace [message #537707 is a reply to message #537702]

Fri, 30 December 2011 06:03

sandeip
Messages: 23
Registered: May 2011
Location: pune

Junior Member

Hi sriram,

i have neither purge sysaux occupants nor cleared any logs or AWR data.

On weekly basis i used to truncate AUD$ table,which is present in SYSTEM Tablespace.

SQL> select table_name,tablespace_name from dba_tables where table_name like 'AU
D$';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM

SQL>

Report message to a moderator

Re: SYSAUX tablespace [message #537712 is a reply to message #537707]

Fri, 30 December 2011 07:03

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

If you monitor v$sysaux_occupants you can determine which components are becoming larger and which are becoming smaller.

Report message to a moderator

Re: SYSAUX tablespace [message #537714 is a reply to message #537702]

Fri, 30 December 2011 07:13

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

Senior Member
Account Moderator

Quote:

@ Michel

You know how to format a querey, good, but does OP know?

Regards
Michel

[Updated on: Fri, 30 December 2011 07:13]

Report message to a moderator

Re: SYSAUX tablespace [message #537715 is a reply to message #537707]

Fri, 30 December 2011 07:14

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

Senior Member
Account Moderator

sandeip wrote on Fri, 30 December 2011 13:03

Hi sriram,

i have neither purge sysaux occupants nor cleared any logs or AWR data.

On weekly basis i used to truncate AUD$ table,which is present in SYSTEM Tablespace.

SQL> select table_name,tablespace_name from dba_tables where table_name like 'AU
D$';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM

SQL>

Michel Cadot wrote on Fri, 30 December 2011 10:39
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Fri, 30 December 2011 07:15]

Report message to a moderator

Previous Topic:	alter database datafile file_id offline
Next Topic:	Database Refresh (from demo to test)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 10:39:42 CDT 2024