Home » SQL & PL/SQL » Client Tools » Claiming information about the DB with SQL (Oracle 11g, SUSE Linux Enterprise Server 11)
Claiming information about the DB with SQL [message #628365] Mon, 24 November 2014 09:15 Go to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Hi,

I am about to write a tool to monitor some vital information of my oracle-DB in a very basic manner.
Therefore I am trying to find a way to read the needed information via sql.
I already found some interesting information, but not all.

So, I hope one of you knows, how I can get the following information with SQL - if possible at all.

Size of the fast recovery area
Usable space in fast recovery area

Regards
Christian
Re: Claiming information about the DB with SQL [message #628367 is a reply to message #628365] Mon, 24 November 2014 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 25796
Registered: January 2009
Location: SoCal
Senior Member
query one of below

SYS V_$FLASH_RECOVERY_AREA_USAGE
SYS DBA_FLASHBACK_ARCHIVE
SYS DBA_FLASHBACK_ARCHIVE_TABLES
SYS DBA_FLASHBACK_ARCHIVE_TS
SYS DBA_FLASHBACK_TXN_REPORT
SYS DBA_FLASHBACK_TXN_STATE
Re: Claiming information about the DB with SQL [message #628368 is a reply to message #628365] Mon, 24 November 2014 09:29 Go to previous messageGo to next message
Dave Linehan
Messages: 2
Registered: September 2014
Location: Dublin, Ireland
Junior Member
A query on V$FLASH_RECOVERY_AREA_USAGE would probably do the trick for you.

[Updated on: Mon, 24 November 2014 09:29]

Report message to a moderator

Re: Claiming information about the DB with SQL [message #628384 is a reply to message #628365] Mon, 24 November 2014 13:09 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try these:
COL Name FOR A20
COL File_Type FOR A32

SELECT Name
     , ROUND ( Space_Limit / 1024 / 1024, 1 ) Mb_Limit
     , ROUND ( Space_Used / 1024 / 1024, 1 ) Mb_Used
     , ROUND ( Space_Reclaimable / 1024 / 1024, 1 ) Mb_Reclaimable
     , Number_Of_Files
  FROM V$recovery_File_Dest;

SELECT * FROM V$flash_Recovery_Area_Usage;

[Updated on: Mon, 24 November 2014 13:17] by Moderator

Report message to a moderator

Re: Claiming information about the DB with SQL [message #628425 is a reply to message #628384] Tue, 25 November 2014 03:21 Go to previous messageGo to next message
chricken
Messages: 13
Registered: November 2014
Junior Member
Thank you all, that should be good for a useful monitor.
Re: Claiming information about the DB with SQL [message #628434 is a reply to message #628425] Tue, 25 November 2014 04:33 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
Have you looked at Oracle's own Enterprise Manager / Cloud Control? You might be reinventing the wheel and introducing an unnecessary layer of complexity.
Re: Claiming information about the DB with SQL [message #628435 is a reply to message #628434] Tue, 25 November 2014 04:39 Go to previous message
chricken
Messages: 13
Registered: November 2014
Junior Member
Hi gazzag,

we are using cloud control and I like it.

But I am building a central overview, that may show me some information with a single look.
I already have information about our mailqueue, errormails and a bit more.
Now there shall be some vital data about oracle on the same page.
Previous Topic: Testing in SQL Developer
Next Topic: SP2-0552: Bind variable "V_D" not declared.
Goto Forum:
  


Current Time: Fri Jan 19 18:38:26 CST 2018

Total time taken to generate the page: 0.01703 seconds