Home » SQL & PL/SQL » SQL & PL/SQL » Tablespace monitoring (merged)
Tablespace monitoring (merged) [message #319917] Tue, 13 May 2008 07:01 Go to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hi,

I have the following query :

select
        a.tablespace_name ,
        tbsize  ,
        tbfree ,
        b.tbfree/a.tbsize*100 "ratio" ,
         b.Largest "Largest space"
from
        ( select tablespace_name,sum(bytes)/1024/1024 tbsize
                from dba_data_files
                group by tablespace_name) a,
        ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
                 max(bytes)/1024/1024 Largest
                from dba_free_space
                group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4  ;


Which gives me the output:

TABLESPACE_NAME                    TBSIZE     TBFREE      ratio Largest space
------------------------------ ---------- ---------- ---------- 
SYSTEM                                200         19        9.5       18.9375
USERS                                  10     6.0625     60.625        5.9375
UNDOTBS1                               80    78.6875  98.359375       78.6875
INDX                                   10     9.9375     99.375        9.9375
TOOLS                                  10     9.9375     99.375        9.9375      9.9375


I want to add another column of current date and time.
i.e every time i fire this command and i get another column which gives me the current time and date for every table space name. How to do so?

Thanks.
Re: Tablespace monitoring [message #319921 is a reply to message #319917] Tue, 13 May 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add SYSDATE in your query.

Regards
Michel
Re: Tablespace monitoring [message #319943 is a reply to message #319917] Tue, 13 May 2008 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sdhanuka wrote on Tue, 13 May 2008 08:01

TABLESPACE_NAME TBSIZE TBFREE ratio Largest space
------------------------------ ---------- ---------- ----------
SYSTEM 200 19 9.5 18.9375
USERS 10 6.0625 60.625 5.9375
UNDOTBS1 80 78.6875 98.359375 78.6875
INDX 10 9.9375 99.375 9.9375
TOOLS 10 9.9375 99.375 9.9375 9.9375
[/code]

I want to add another column of current date and time.
i.e every time i fire this command and i get another column which gives me the current time and date for every table space name. How to do so?



What purpose would this serve? It would be the same value for each row. Addtionally, how does your output have no underline under the "Largest Space" heading, and and what is that phantom column which appears on the TOOLS row?
Re: Tablespace monitoring [message #319981 is a reply to message #319943] Tue, 13 May 2008 10:21 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hi joydivision.

Yes its not a compulsion for me to add the value for each row. My purpose is that i need to monitor the tablespace over a period of time and compare the growth from a particular date upto present time. And about the line i did copy & paste mistake.

Many thanks Michel for you answer

[Updated on: Tue, 13 May 2008 10:21]

Report message to a moderator

Re: Tablespace monitoring [message #320002 is a reply to message #319981] Tue, 13 May 2008 12:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Is this a database you created? Do realize that the datafile of the tablespaces are probably set to autoextend, which would make the output not as relevant as you may want. If you just want o see how much is used, then yes, but if you are looking at ratio, then it doesn't help. Additionally, what meaning would the largest space column have?
In the old days, before the turn of the century, before locally managed tablespaces, one way need to know this, but not these days. Is this a script from days gone by?
Re: Tablespace monitoring [message #320068 is a reply to message #319981] Wed, 14 May 2008 00:17 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

I have attached a text file. This file contains sqls and steps to implement.

This reports DB growth Day wise, which may help you.

You can modify and use it how you want.

Regards,

MSMallya
  • Attachment: freeinst.txt
    (Size: 13.56KB, Downloaded 469 times)
Re: Tablespace monitoring [message #321025 is a reply to message #320068] Sun, 18 May 2008 02:42 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey many thinks for your procedures...i will finish working on my solution and try your steps...

Thanks.
Tablespace monitoring [message #321685 is a reply to message #319917] Wed, 21 May 2008 01:15 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey,

I created a table called tbs with the table definition


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 timestamp                                          VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 TBSIZE                                             NUMBER
 TBFREE                                             NUMBER
 ratio                                              NUMBER
 Largest space                                      NUMBER


I want to create a pl/sql procedure such that it inserts data into this table whenever there is growth in any of the tablespaces.

Should i use triggers for the same.

please tell me what procedure to follow for the same.

Or should i create an sql script and put it on a scheduler that will insert data everyday.

sql script would be


insert into tbs select to_char(sysdate,'DD-MM-YYY HH24:MI:SS') as timestamp,
        a.tablespace_name ,
        tbsize  ,
        tbfree ,
        b.tbfree/a.tbsize*100 "ratio" ,
         b.Largest "Largest space"
from
        ( select tablespace_name,sum(bytes)/1024/1024 tbsize
                from dba_data_files
                group by tablespace_name) a,
        ( select tablespace_name,sum(bytes)/1024/1024 tbfree,
                 max(bytes)/1024/1024 Largest
                from dba_free_space
                group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4  ;




Thanks
Re: Tablespace monitoring [message #321699 is a reply to message #321685] Wed, 21 May 2008 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you continue on your previous topic?

timestamp is a reserved word, don't use it.
Do you really have column names in lower and mixed cases and even with spaces!?

Have a look at:
http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=1770

Regards
Michel
Re: Tablespace monitoring [message #321700 is a reply to message #321699] Wed, 21 May 2008 01:45 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
alright, i will make the changes you have asked me to. And the continue.

Sorry for creating a new post.

Alright your script does want i wanted to achieve...but it is a little complex for me to follow. Can i work on the lines i have mentioned above???

[Updated on: Wed, 21 May 2008 01:52]

Report message to a moderator

Re: Tablespace monitoring [message #321710 is a reply to message #321700] Wed, 21 May 2008 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can i work on the lines i have mentioned above?

Yes, if it gives the data you want.

Regards
Michel
Re: Tablespace monitoring [message #321722 is a reply to message #321710] Wed, 21 May 2008 02:46 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
So should i create pl/sql procedure using triggers or put the sql script on a scheduler???
Re: Tablespace monitoring [message #321725 is a reply to message #321722] Wed, 21 May 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no trigger type for that.
Use a job, see the link I posted.

Regards
Michel
Re: Tablespace monitoring [message #321726 is a reply to message #321725] Wed, 21 May 2008 02:55 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
yes i saw the link.

It is a little complex for me. I could copy paste it and create the output. But i wanted to try something on my own Smile
Re: Tablespace monitoring [message #321731 is a reply to message #321726] Wed, 21 May 2008 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use it as an example to create a job without taking all the code.

Regards
Michel
Re: Tablespace monitoring [message #321743 is a reply to message #321731] Wed, 21 May 2008 03:49 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Ok i can do that.

So you suggest to create a table and then write a job which will populate the table?

We cannot use triggers when a tablespace size increases ??
Re: Tablespace monitoring [message #321754 is a reply to message #321743] Wed, 21 May 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So you suggest to create a table and then write a job which will populate the table?

Yes.

Quote:
We cannot use triggers when a tablespace size increases ??

There is no such trigger.

Regards
Michel
Re: Tablespace monitoring [message #321780 is a reply to message #321754] Wed, 21 May 2008 05:23 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Thank you Michel
Re: Tablespace monitoring [message #322780 is a reply to message #321780] Mon, 26 May 2008 04:05 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey

A few more doubts regarding tablespace monitoring.

Ideally when we want to monitor a tablespace and generate report, on what parameters should we monitor it. some of them are:

Free Space
Used Space
Avail Space
Datafile name
%free

What other parameters do we look for.

How would one check if your tablespace size %=> 95%

Thanks
Re: Tablespace monitoring [message #322988 is a reply to message #322780] Tue, 27 May 2008 00:21 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey,

If one wants to monitor the tablespace based on the following metrics:

-Max File Size
-Used File Size
-Total File Size
-95%

What data dictionary views should we query ?
DBA_TABLES
DBA_EXTENTS
DBA_TABLESPACE
DBA_FREE_SPACE
DBA_SPACE_USAGE

Thanks.
Re: Tablespace monitoring [message #322995 is a reply to message #322988] Tue, 27 May 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove dba_tables.
Change dba_extents to dba_segments.
Change dba_tablespace to dba_data_files.
dba_space_usage does not exist.

Regards
Michel
Re: Tablespace monitoring [message #323004 is a reply to message #322995] Tue, 27 May 2008 00:59 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
ok,


see this


SQL> select tablespace_name, sum(bytes) from dba_segments group by tablespace_na
me;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
SYSTEM                          189726720
UNDOTBS1                          1228800
USERS                              917504

SQL> select tablespace_name, sum(bytes) from dba_data_files group by tablespace_
name;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
INDX                             10485760
SYSTEM                          209715200
TOOLS                            10485760
UNDOTBS1                         83886080
USERS                            10485760


What is the difference why isn't segments showing all tablespaces.

In Your link above you have used dba_extents to monitor your tablespace.

Also do you suggest any other metrics for monitoring one's tablespace in a production database.

[Updated on: Tue, 27 May 2008 01:01]

Report message to a moderator

Re: Tablespace monitoring [message #323011 is a reply to message #323004] Tue, 27 May 2008 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be easier to read the result if you added an ORDER BY clause.

Quote:
What is the difference why isn't segments showing all tablespaces.

It seems INDX and TOOLS does not contain any objects.

Quote:
In Your link above you have used dba_extents to monitor your tablespace.

And I was right, and here wrong, dba_segments does not have the breakdown per file.

Other metric? Yes, capacity planning, estimate (from the history) when the file/tablespace will reach a critical level.

Regards
Michel

Re: Tablespace monitoring [message #323022 is a reply to message #323011] Tue, 27 May 2008 01:59 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey,

How would we do an estimate (from the history) when the file/tablespace will reach a critical level.

Please have a look at the following queries.

SQL> select tablespace_name, sum(bytes) from dba_data_files group by tablespace_
name order by 2;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
INDX                             10485760
TOOLS                            10485760
USERS                            10485760
UNDOTBS1                         83886080
SYSTEM                          209715200



SQL>  select tablespace_name, sum(bytes) from dba_segments group by tablespace_n
ame order by 2;

TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
USERS                              917504
UNDOTBS1                          1228800
SYSTEM                          189726720


shouldn't the values in the second column be the same. A tablespace is allocated an segment which in turn contains extents.
Re: Tablespace monitoring [message #323028 is a reply to message #323022] Tue, 27 May 2008 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How would we do an estimate (from the history) when the file/tablespace will reach a critical level.

Follow the follow up at the bottom of the page in the link I posted.

Quote:
shouldn't the values in the second column be the same

No, one give the size of the files and the other one the size of allocated space to segments in these files. In addition, in files, you have Oracle overhead and, maybe, free space.

Regards
Michel
Re: Tablespace monitoring [message #323031 is a reply to message #323028] Tue, 27 May 2008 02:23 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Aright I will follow up with your article.

Quote:
No, one give the size of the files and the other one the size of allocated space to segments in these files. In addition, in files, you have Oracle overhead and, maybe, free space.


So when we want to check for the used space of tablespace we will query the dba_data_file view and not the the dba_extents right??
Re: Tablespace monitoring [message #323044 is a reply to message #323031] Tue, 27 May 2008 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know the space used inside files by objects, you have to query dba_extents.
To know the size of the file, you have to query dba_data_files.
To know the overall used space, you have to compute the difference between dba_data_files and dba_free_space.

Regards
Michel
Re: Tablespace monitoring [message #323048 is a reply to message #323044] Tue, 27 May 2008 03:19 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Oh clearer.

Thanks again Michel.

I will come with more doubts once i have created the entire query Smile...
Re: Tablespace monitoring [message #323098 is a reply to message #323048] Tue, 27 May 2008 05:16 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hi I have written the sql script given below for monitoring the tablespace:

select db_name, tablespace,db_files,TB_SIZE,nvl(free_space,0) "TB_FREE",(free_space/total_space)*100 "FREE%"  
from (select name db_name from v$database),(select tablespace_name tablespace
from dba_data_files   
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/(1024*1024*1024) tb_size
from dba_data_files group by tablespace_name) b,
( select sum(decode(autoextensible,'YES',maxbytes/(1024*1024*1024),
bytes/(1024*1024*1024))) 
total_space,tablespace_name,count(*) db_files from sys.DBA_data_files group by tablespace_name ) total,
( select tablespace_name,sum(bytes/(1024*1024*1024)) free_space 
from sys.DBA_Free_space group by tablespace_name) free 
where a.tablespace=total.tablespace_name and total.tablespace_name=free.tablespace_name
 and a.tablespace=b.tablespace_name
and free.tablespace_name=a.tablespace and (free_space/total_space)*100<=5;


If i want to get the same data for a remote server, I would use a dblink and populate that data in the table i have created above.

How to use the dblink to query the remote db using the above query

[Updated on: Tue, 27 May 2008 05:18]

Report message to a moderator

Re: Tablespace monitoring [message #323100 is a reply to message #323098] Tue, 27 May 2008 05:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try SQL Formatter and repost.

Regards
Michel
Re: Tablespace monitoring [message #323112 is a reply to message #323100] Tue, 27 May 2008 05:50 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey...thanks i did not know about SQL formatter Smile.

I got the solution myself.

we have to use view_name@db_10g for all views right. I tried individually for all views on my system where i have a vmware server for all the views i have mentioned in the query below

SELECT db_Name,
       TABLESPACE,
       db_Files,
       Tb_Size,
       Nvl(Free_Space,0) "TB_FREE",
       (Free_Space / Total_Space) * 100 "FREE%"
FROM   (SELECT NAME db_Name
        FROM   v$DataBase),
       (SELECT   TableSpace_Name TABLESPACE
        FROM     dba_Data_Files
        GROUP BY TableSpace_Name) a,
       (SELECT   TableSpace_Name,
                 SUM(Bytes) / (1024 * 1024 * 1024) Tb_Size
        FROM     dba_Data_Files
        GROUP BY TableSpace_Name) b,
       (SELECT   SUM(DECODE(AutoexTensible,'YES',MaxBytes / (1024 * 1024 * 1024),
                                           Bytes / (1024 * 1024 * 1024))) Total_Space,
                 TableSpace_Name,
                 COUNT(* ) db_Files
        FROM     sys.dba_Data_Files
        GROUP BY TableSpace_Name) Total,
       (SELECT   TableSpace_Name,
                 SUM(Bytes / (1024 * 1024 * 1024)) Free_Space
        FROM     sys.dba_Free_Space
        GROUP BY TableSpace_Name) Free
WHERE  a.TABLESPACE = Total.TableSpace_Name
       AND Total.TableSpace_Name = Free.TableSpace_Name
       AND a.TABLESPACE = b.TableSpace_Name
       AND Free.TableSpace_Name = a.TABLESPACE
       AND (Free_Space / Total_Space) * 100 <= 5;



Also Now the issue is I have created a table using the above query and a batch script(LIKE A JOB or something like crontab) which when ran will insert value into this table from all the database on remote server.

When i query this table i want to omit the database name column and instead i will create a heading for each dbname on the HTML report.

How to do so.

I hope i have explained properly .
Re: Tablespace monitoring [message #323132 is a reply to message #323112] Tue, 27 May 2008 07:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like (writing it on the fly, not tested):
set pages 0
set feed off
set lines 200
set trimspool on
set echo off
set verify off
spool script
select distinct 
'spool '||db_name||'
set markup html on
select <what I want> from mytable where db_name='''||db_name||'';'||'
spool off
set markup html off'
from my_table
order by db_name
/
spool off
set pages XXX
@script.lst

It generates one html file per database.

Regards
Michel
Re: Tablespace monitoring [message #323261 is a reply to message #323132] Wed, 28 May 2008 00:04 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Hey

I want the output of all the databases on one page itself and all columns should appear on HTML page other than the Db_name column.

Re: Tablespace monitoring [message #323263 is a reply to message #323261] Wed, 28 May 2008 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So just select the other columns.
Execute a query per database.
Take my script as an example.

Regards
Michel
Re: Tablespace monitoring [message #323286 is a reply to message #323263] Wed, 28 May 2008 01:27 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
hey the spooling report code is

set arraysize 1 
set heading on
set feedback off 
set verify off
set linesize 150
set pages 9999
set echo off 
set term off feed off trimspool on RECSEP off
set markup html on spool on entmap off preformat off 
spool c:\oracle\ora92\apache\apache\scripts\tableSpace.html 

prompt <h1 align="center">Tablespace Monitoring Report</h1>
prompt </hr>

prompt <h3>Report for Test</h3>
connect test/test@test
@C:\oracle\ora92\apache\apache\scripts\tablespace_95.sql


spool off
set markup html off
exit



This is the sql script which inserts data from remote server databases using DBLink for all databases into the table i have created on my system
INSERT INTO Tb
SELECT db_Name,
       TABLESPACE,
       db_Files,
       Tb_Size,
       Nvl(Free_Space,0) Tb_Free,
       (Free_Space / Total_Space) * 100 "FREE%"
FROM   (SELECT NAME db_Name
        FROM   v$DataBase),
       (SELECT   TableSpace_Name TABLESPACE
        FROM     dba_Data_Files
        WHERE    TableSpace_Name NOT LIKE 'TEMP%'
        GROUP BY TableSpace_Name) a,
       (SELECT   TableSpace_Name,
                 SUM(Bytes) / (1024 * 1024 * 1024) Tb_Size
        FROM     dba_Data_Files
        GROUP BY TableSpace_Name) b,
     (SELECT SUM(DECODE(AutoexTensible,'YES',MaxBytes /(1024 * 1024 * 1024),
                  Bytes / (1024 * 1024 * 1024))) Total_Space,
                 TableSpace_Name,
                 COUNT(* ) db_Files
        FROM     sys.dba_Data_Files
        GROUP BY TableSpace_Name) Total,
       (SELECT   TableSpace_Name,
                 SUM(Bytes / (1024 * 1024 * 1024)) Free_Space
        FROM     sys.dba_Free_Space
        GROUP BY TableSpace_Name) Free
WHERE  a.TABLESPACE = Total.TableSpace_Name
       AND Total.TableSpace_Name = Free.TableSpace_Name
       AND a.TABLESPACE = b.TableSpace_Name
       AND Free.TableSpace_Name = a.TABLESPACE
       AND (Free_Space / Total_Space) * 100 <= 5;

SELECT TABLESPACE,
       db_Files,
       Tb_Size,
       Tb_Free,
       "FREE%"
FROM   Tb where db_name="";



Now the issue is i have to get the data from 84 databses using dblink and insert into this table so will i have to write 84 seperate scripts? Is there a better method to do so ???

Main purpose of this scripts are to display old tablespaces on the report for all databases as well as in to have a date column and tablespaces stats from x date to y date for which i need to keep the information in a table.

Flashback queries can be used here??

[Updated on: Wed, 28 May 2008 01:32]

Report message to a moderator

Re: Tablespace monitoring [message #323300 is a reply to message #323286] Wed, 28 May 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now the issue is i have to get the data from 84 databses using dblink and insert into this table so will i have to write 84 seperate scripts? Is there a better method to do so ???


Use PL/SQL and loop on the database names that you previously recorded in a PL/SQL or permanent table.

Quote:
Main purpose of this scripts are to display old tablespaces on the report for all databases as well as in to have a date column and tablespaces stats from x date to y date for which i need to keep the information in a table.

Record the current timestamp along with other data.

Regards
Michel
Re: Tablespace monitoring [message #323323 is a reply to message #323300] Wed, 28 May 2008 03:43 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
Yes i will add a timestamp column to it .

Alright so i do not need to write 84 separate scripts ..one pl/sql will connect to all the dbs using the db links and populate the table. ?
Re: Tablespace monitoring [message #323339 is a reply to message #323323] Wed, 28 May 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Tablespace monitoring [message #323343 is a reply to message #323339] Wed, 28 May 2008 04:37 Go to previous messageGo to next message
sdhanuka
Messages: 173
Registered: March 2008
Senior Member
How Sad...all will have different DBlinks and diff db names.

One more question regarding the tablespaces we can use sys.sm$ts_free/avail/used to get tablespace stats then why does one use dba_Free_space , dba_data_files ??
Re: Tablespace monitoring [message #323349 is a reply to message #323343] Wed, 28 May 2008 04:51 Go to previous messageGo to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How ...all will have different DBlinks and diff db names.


reply
loop on the database names that you previously recorded in a PL/SQL or permanent table.


Quote:
One more question regarding the tablespaces we can use sys.sm$ts_free/avail/used to get tablespace stats then why does one use dba_Free_space , dba_data_files ??

SQL> @view SM$TS_FREE

SM$TS_FREE
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER

select tablespace_name, sum(bytes) bytes from dba_free_space
    group by tablespace_name

SQL> @view SM$TS_USED

SM$TS_USED
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER

select tablespace_name, sum(bytes) bytes from dba_segments
    group by tablespace_name

SQL> @view SM$TS_AVAIL

SM$TS_AVAIL
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER

select tablespace_name, sum(bytes) bytes from dba_data_files
    group by tablespace_name

Regards
Michel
Previous Topic: help needed on query
Next Topic: how to select 1st and 15th of every month
Goto Forum:
  


Current Time: Fri Dec 02 12:20:50 CST 2016

Total time taken to generate the page: 0.10432 seconds