DBA QUERYING TABLES & VIEWS TIPS

DBA - TIPS (Querying Tables & Views): Every DBA Must Know.

Checking Invalid Objects

SELECT 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
FROM dba_objects
WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER','VIEW') and status = 'INVALID' ORDER BY OBJECT_NAME;

Determine the Usuage of tablespaces

SELECT        *

FROM            dba_tablespace_usage_metrics

ORDER BY   used_percent desc;

Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZE

For Data Files

SELECT     file_name, bytes, autoextensible, maxbytes

FROM       dba_data_files

WHERE     tablespace_name='<tablespace name> ';

For Temp Files

SELECT     file_name, bytes, autoextensible, maxbytes

FROM       dba_temp_files

WHERE      tablespace_name='<tablespace name>’;

Check How Many Datafiles a Tablespace Has or what all datafiles a tablespace contains

SELECT file_name, tablespace_name

FROM dba_data_files

WHERE tablespace_name =’<tablespace name>’;

Find the TABLESPACE of a table.
SELECT tablespace_name
FROM   all_tables
WHERE  table_name = 'OURTABLENAME';

Find the Min Extent, Max Extent, BLOCKS, OWNER, BYTES, INITIAL_EXTENT of a Table        
SELECT *
FROM   dba_segments
WHERE  SEGMENT_NAME = 'FND_CONCURRENT_REQUESTS';        

Find Size of FND_CONCURRENT_REQUESTS TABLE 
SELECT (bytes/1024)/1024 "Size in KB"
FROM   dba_segments
WHERE  segment_name = 'FND_CONCURRENT_REQUESTS';

Query to find which user has locked the table
Select  *
from    dba_waiters --This will give you the sid of the blocker         
OR
You can run script utllockt.sql which is present in $ORACLE_HOME/rdbms/admin.
Running this gives you a nice tree-like report of who's waiting and who's blocking.
OR
SELECT   s.sid,s.serial#, s.username,l.os_user_name,s.terminal,
                 d.object_id,d.object_name
FROM      v$session s, dba_objects d, v$locked_object l
WHERE    s.sid = l.session_id and d.object_id = l.object_id;

Find total number of records in a table
SELECT  COUNT(*)
FROM     tablename;

What is my current session id ?

The username, program, machine, terminal, session id, serial # and more can be found the v$session   view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session.

The query could be:

SELECT  username, sid, serial#, program
FROM     V$session
WHERE   audsid = userenv('sessoinid');

How to terminate a session ?
ALTER SYSTEM KILL SESSION 'sid, serial#';

Which database am I connected to? As which user?
SELECT  global_name
FROM      global_name;

To find the user, from SQLPLUS
SHOW USER

What is the SGA size ?
SHOW SGA 
Or
 SELECT  *
FROM      V$sga;

Where are my alert log and dump files written ?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora   (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK"

How to increase the size of a TABLESPACE ?
ALTER DATABASE DATAFILE 'filename' RESIZE nn M;
OR
You can add more physical data files to the tablespace by

ALTER TABLESPACE <tablespace-name>
ADD DATAFILE 'filename' SIZE nn M;
Make sure you specify the full path name for the filename

Is my database running in Archive log mode?
Where are the archived files written?
ARCHIVE LOG LIST