OraFAQ Forum: Server Administration » How to find when the datafile was resized?

Home » RDBMS Server » Server Administration » How to find when the datafile was resized? (10g/11g)

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

How to find when the datafile was resized? [message #539599]

Tue, 17 January 2012 05:56

luckbychance
Messages: 5
Registered: January 2012

Junior Member

Hi All

We need a small help. We need to find when any datafile was resized ( if at all)in a tablespace. Actually, by noting the created date from v$datafile , we used to know the data growth in a tablespace. Now as the number of datafiles have increased, we want to resize them.
This diagnostinc have to be done without changing/adding anything in DB.

Thanks in advance..

Report message to a moderator

Re: How to find when the datafile was resized? [message #539612 is a reply to message #539599]

Tue, 17 January 2012 06:36

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

Senior Member
Account Moderator

Only one place you will find this information for ALTER DATABASE DATAFILE: alert.log
If increase comes from an auto-extend, then it is recorded no where.

Regards
Michel

Report message to a moderator

Re: How to find when the datafile was resized? [message #539617 is a reply to message #539612]

Tue, 17 January 2012 06:45

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hello Michel

there is a view KU$_FILE_VIEW

SQL> desc KU$_FILE_VIEW
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(513)
 FNAME                                              VARCHAR2(1026)
 FSIZE                                              NUMBER
 RESIZE                                             NUMBER
 MAXEXTEND                                          NUMBER
 INC                                                NUMBER
 TS_NUM                                             NUMBER
 IS_OMF                                             NUMBER

where Oracle records resize values.

So it should be possible to write a monitoring script.

Regards
Leonid

Report message to a moderator

Re: How to find when the datafile was resized? [message #539619 is a reply to message #539612]

Tue, 17 January 2012 06:46

luckbychance
Messages: 5
Registered: January 2012

Junior Member

the command issued will be

ALTER DATABASE DATAFILE '/..../' RESIZE 5000M; on already existing datafile.

Any other way to find data growth in a tablespace datewise like in last 5 days, last week etc..

regards..

[Updated on: Tue, 17 January 2012 07:10]

Report message to a moderator

Re: How to find when the datafile was resized? [message #539622 is a reply to message #539617]

Tue, 17 January 2012 07:14

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

Senior Member
Account Moderator

Quote:

there is a view KU$_FILE_VIEW...
So it should be possible to write a monitoring script.

Maybe, but:
1/ I'm not really for using internal views
2/ You can use the same monitoring on dba_data_files

Regards
Michel

Report message to a moderator

Re: How to find when the datafile was resized? [message #539623 is a reply to message #539619]

Tue, 17 January 2012 07:15

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

Senior Member
Account Moderator

Quote:

the command issued will be

ALTER DATABASE DATAFILE '/..../' RESIZE 5000M; on already existing datafile.

This command is recorded in alert.log file.

Regards
Michel

Report message to a moderator

Re: How to find when the datafile was resized? [message #539640 is a reply to message #539623]

Tue, 17 January 2012 09:41

luckbychance
Messages: 5
Registered: January 2012

Junior Member

Apart from alert log, anyway to find the tablespace growth.. some DBA_HIST_ etc plz? Internal views also OK. We want manual steps.

Regards..

Report message to a moderator

Re: How to find when the datafile was resized? [message #539641 is a reply to message #539640]

Tue, 17 January 2012 09:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>We want manual steps.

1) CREATE TABLE TABLESPACE_SIZE_HIST ...
2) periodically INSERT INTO TABLESPACE_SIZE_HIST ...
3) SELECT * FROM TABLESPACE_SIZE_HIST

Report message to a moderator

Re: How to find when the datafile was resized? [message #539642 is a reply to message #539640]

Tue, 17 January 2012 09:53

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You can write a monitoring script based on dd views as Michel and I suggested or based on alertlog. For the last purpose you can use external table for ex.

Report message to a moderator

Re: How to find when the datafile was resized? [message #539643 is a reply to message #539640]

Tue, 17 January 2012 10:13

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

Senior Member
Account Moderator

Quote:

Apart from alert log, anyway to find the tablespace growth.. some DBA_HIST_ etc plz?

Yes there are many but we don't want to tell you because we are real bastards.

Do you really think we would not mention them if they exist?

Regards
Michel

Report message to a moderator

Re: How to find when the datafile was resized? [message #539715 is a reply to message #539599]

Wed, 18 January 2012 04:08

luckbychance
Messages: 5
Registered: January 2012

Junior Member

Hi All

I apologize if i was not able to convey myself properly.

One history view i found on Documentation ( Refrence )

DBA_HIST_TBSPC_SPACE_USAGE

desc DBA_HIST_TBSPC_SPACE_USAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                            NUMBER
 DBID                                      NOT NULL NUMBER
 TABLESPACE_ID                                      NUMBER
 TABLESPACE_SIZE                                    NUMBER
 TABLESPACE_MAXSIZE                                 NUMBER
 TABLESPACE_USEDSIZE                                NUMBER
 RTIME                                              VARCHAR2(25)

( Sorry, i dont know the formatted way)

As we see the rtime is a varchar2 and not date. If its ok to ask .. i want to request you to help to write a sql query to find tablespace growth in last 3 days.. I am not very good on it.

Thanks in advance..

regards..

Report message to a moderator

Re: How to find when the datafile was resized? [message #539720 is a reply to message #539715]

Wed, 18 January 2012 04:43

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

Senior Member
Account Moderator

Pöst what you tried and where are stuck.

Regards
Michel

Report message to a moderator

Re: How to find when the datafile was resized? [message #540330 is a reply to message #539599]

Sun, 22 January 2012 01:56

luckbychance
Messages: 5
Registered: January 2012

Junior Member

Apilogies for late reply

 select distinct TABLESPACE_ID,v.name, (a.TABLESPACE_MAXSIZE-b.TABLESPACE_MAXSIZE ) tbs_growth 
from 
(select TABLESPACE_MAXSIZE 
from  DBA_HIST_TBSPC_SPACE_USAGE where rtime = (select min(rtime) 
from DBA_HIST_TBSPC_SPACE_USAGE 
where trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')) = trunc(sysdate - 3))) a ,
(select TABLESPACE_MAXSIZE from  DBA_HIST_TBSPC_SPACE_USAGE 
where rtime = (select max(rtime) 
from DBA_HIST_TBSPC_SPACE_USAGE) ) b , v$tablespace v ,DBA_HIST_TBSPC_SPACE_USAGE d
where v.ts#=d.TABLESPACE_ID and v.name like upper('&tbs')  ;

But found one Bug:bug 5616718 .. and The query above is not giving correct info..

Regards..

[Updated on: Sun, 22 January 2012 02:17] by Moderator

Report message to a moderator

Previous Topic:	About dba_sys_privs
Next Topic:	rollback segment too small ora-01555

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

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