Question 86 :
What is Auditing ?
Monitoring of user access to aid in the investigation of database use.
Question 87 :
How does one see the uptime for a database? (for DBA )
Look at the following SQL query:
SELECT to_char (startup_time,'DD-MON-YYYY HH24: MI: SS') "DB Startup Time"
Marco Bergman provided the following alternative solution:
SELECT to_char (logon_time,'Dy dd Mon HH24: MI: SS') "DB Startup Time" FROM sys.v_$session WHERE Sid=1 /* this is pmon */
Users still running on Oracle 7 can try one of the following queries:
Column STARTED format a18 head 'STARTUP TIME' Select C.INSTANCE, to_date (JUL.VALUE, 'J') || to_char (floor (SEC.VALUE/3600), '09') || ':' -- || Substr (to_char (mod (SEC.VALUE/60, 60), '09'), 2, 2) || Substr (to_char (floor (mod (SEC.VALUE/60, 60)), '09'), 2, 2) || '.' || Substr (to_char (mod (SEC.VALUE, 60), '09'), 2, 2) STARTED from SYS.V_$INSTANCE JUL, SYS.V_$INSTANCE SEC, SYS.V_$THREAD C Where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%'; Select to_date (JUL.VALUE, 'J') || to_char (to_date (SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED from SYS.V_$INSTANCE JUL, SYS.V_$INSTANCE SEC where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%'; select to_char (to_date (JUL.VALUE, 'J') + (SEC.VALUE/86400), -Return a DATE 'DD-MON-YY HH24:MI:SS') STARTED from V$INSTANCE JUL, V$INSTANCE SEC where JUL.KEY like '%JULIAN%' and SEC.KEY like '%SECOND%';
Question 88 :
Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA )
Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:
SELECT * FROM v$tempfile; SELECT * FROM dba_temp_files;
Question 89 :
How do I find used/free space in a TEMPORARY tablespace? (for DBA )
Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:
SELECT tablespace_name, SUM (bytes used), SUM (bytes free) FROM V$temp_space_header GROUP BY tablespace_name;
Question 90 :
How can one see who is using a temporary segment? (for DBA )
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks from sys.v_$session s, sys.v_$sort_usage u where s.addr = u.session_addr / select s.osuser, s.process, s.username, s.serial#, Sum (u.blocks)*vp.value/1024 sort_size from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter VP where s.saddr = u.session_addr and vp.name = 'db_block_size' and s.osuser like '&1' group by s.osuser, s.process, s.username, s.serial#, vp.value /