Oracle Concepts – Find indexes for a table

See the indexes for a table

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15

SQL> column table_name format a20

SQL> column index_name format a20

SQL> column column_name format a20

SQL> Select owner, table_name, index_name, column_name 2  FROM dba_ind_columns Order by owner, table_name, column_name  Where owner=’SCOTT’  AND table_name=’EMP’;

NOTE: You normally rebuild an index when moving it to a different tablespace or when it becomes invalid as the table relocates to a different tablespace. Rebuilding indexes on a regular basis is not required and
should not be practiced.
— Rebuild an index
ALTER INDEX salary_idx REBUILD;
To drop an index, use the DROP INDEX command, as shown in the following screenshot:
— Permanently remove the index from the database
DROP INDEX salary_idx;

Advertisements

Oracle : TKPROF simple steps

1. Set the following parameters
TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE = unlimited (also see metalink article 108723.1)
USER_DUMP_DEST = /oracle/admin/ora9i/udump

2. Enable SQL TRACE for a session
alter session set SQL_TRACE true;

3. Run the query

4.obtain the number included in the trace filename
Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1)

or

Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select userenv(‘sid’) from dual)

5.Run TKPROF at the command line to put the TRACE file into readable format
tkprof ora_19554.trc rich2.prf explain=system/manager sort=FCHCPU,EXECPU,PRSCPU print=5

Bitmap & B-Tree indexes

What is Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Oracle’s two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

What is Bitmap Index?

A bitmap index is an indexing method that can provide both performance benefits and storage savings. Bitmap indexes are particularly useful for data warehousing environments because data is usually updated less frequently and ad hoc queries are more common.

Bit-mapped indexes are best for “low-cardinality” data (such as a column to indicate a person’s gender, which contains only two possible values: MALE and FEMALE).

Characteristic of Bitmap Indexes:

        1.  For columns with very few unique values (low cardinality)

        2.  Tables that have no or little insert/update are good candidates (static data in warehouse)

        3.  Stream of bits: each bit relates to a column value in a single row of table.

What is B-Tree Index?

B-tree indexes are usually created on columns containing the most unique values. It is an ordered set of entries in which each entry a search key value and a pointer to a specific row with that value. When a server finds a search key value matching a constraint, the pointer is used to fetch the row.

Referrence:  http://tamzidul-amin.blogspot.com/2010/03/index-bitmap-index-b-tree-index.html

Block change tracking (BCT)

With Oracle 10gr1 Enterprise Edition Oracle introduced Block change tracking (BCT) feature which is used to FAST / Speed up RMAN Incremental Backup. In Oracle 9i we could create incremental backups with level 0 to 4, level 0 backup is a full backup. In Oracle 10g there are still these levels but we only use incremental level 0 and 1 backups with Oracle’s suggested backup strategy.

Before oracle 10g, During RMAN Incremental backup oracle used to read every block in database and compare the SCN in the block with the SCN in the base backup. If the block’s SCN is greater than the SCN in the backup then the block is a candidate for the New Incremental backup.  But with this BCT feature oracle instead of reading the entire database blocks it just directly reads only changed blocks results in saving lot of time.

Once BCT is enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN’s performance as it does not have to scan whole datafiles instead to detect changed blocks.

Logging of changed blocks is performed by the CTWR (change tracking writer) process which is also NEW background process introduced in 10g and responsible for writing data to the BCT file.  By default, the Block change tracking file is created as Oracle managed file in DB_CREATE_FILE_DEST specified location.

By default, Oracle will not record block change information. You can Enable or disable the change tracking when the database is open or mounted.

Enable Block Change tracking and set location

SQL> alter system set db_create_file_dest=’location’ SCOPE=BOTH;

SQL> alter database enable block change tracking;

Manually specify location for the block change tracking

SQL>alter database enable block change tracking using file ‘location’;

Disable block change tracking

SQL> alter database disable block change tracking;

Moving Block Change tracking file without Database shutdown:
If your database is 24×7 critical production and you cannot shut down, then follow the below steps. Please note that you must disable change tracking and you will lose the old contents of the change tracking file, if you choose this method.

Step1: Disable the change tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Step2: Re-enable it at the new location
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new_location’;

Moving Block Change tracking file with Database shutdown:
Step1: Determine the change tracking file:
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

Step2: shutdown and move or copy the tracking file.
SQL> SHUTDOWN IMMEDIATE
$ cp ‘/old_lockation/block_change_tracking.ora’ ‘/new_location/ block_change_tracking.ora’

Step3: Mount the database and rename change tracking file to new location.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE ‘old_location’ TO ‘new_location’;

Step4: Open the database
SQL> ALTER DATABASE OPEN;

Block change tracking in RAC (Real Applications Clusters) environment, the file must be located on shared storage so that the file is accessible for all the nodes. BCT file is one per db, therefore BCT file is created in location defined by parameter db_create_file_dest

SQL> desc    v$block_change_tracking;

Name                        Null?                      Type

——————-      ————–             ——————

STATUS                                                 VARCHAR2(10)

FILENAME                                            VARCHAR2(513)

BYTES                                                    NUMBER

Change Tracking Writer (CTWR)

As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.
You can view the size of the CTWR dba buffer by looking at v$sgastat
SQL> SELECT * FROM v$sgastat  WHERE name like ‘CTWR%’; 

Differences between STATSPACK and AWR

  1. AWR is the next evolution of statspack utility.
  2. AWR is the 10g NEW feature but statspack can still be used in 10g.
  3. AWR repository holds all the statistics available in statspack as well as some additional statistics which are not (10g new features).
  4. Statspack does not STORE the ASH statistics which are available in AWR dba_hist_active_sess_history VIEW.
  5. Important difference between both is STATSPACK doesn’t store history for new metric statistics introduced in Oracle 10g.The key AWR views dba_hist_sysmetric_history  and  dba_hist_sysmetric_summary.
  6. AWR contains views such as dba_hist_service_stat,  dba_hist_service_wait_class and dba_hist_service_name.
  7. Latest version of statspack included with 10g contains a specific tables which track history of statistics that reflect the performance of Oracle streams feature. These tables are stats$streams_capture, stats$treams_apply_sum, stats_buffered_subscribers, stats$rule_set.
  8. The AWR does not contain specific tables that reflect oracle streams activity. Therefore if DBA relies on Oracle streams it would be useful to monitor its performance using Statspack utiity.
  9. AWR snapshots are scheduled every 60 minutes by default.
  10. Statspack snapshot purges must be scheduled manually but AWR snapshots are purged automatically by MMON every night.

More about AWR click here
Steps to generate AWR report click here

Oracle TKPROF

The TKPROF (Transient Kernel Profiler) is an Oracle database utility which converts Oracle trace files into a more human readable form.

Recently a developer wants me to send his trace file output for a particular trace file in a flatfile

Then I performed the below steps:

SQL> show parameter diag;

NAME                                     TYPE         VALUE

———————————- ———–   ——————————

diagnostic_dest                    string      /opt/oracle/app

Then go to diag location and go to the trace location and perform the following

$ tkprof  DEV71_ora_24961_INFO_PKG.trc   dba.txt   sys=no

Here in the above I’m putting the trace file output in a dba.txt file

Tkprof parameters:

TRACEfile : The name of the TRACE file containing the statistics by SQL_TRACE.

Output_file: The name of the file where TKPROF writes its output.

SORT= parameters

The order in which to display the statements in the output. There are about 20 different options for sorting the output-you can even combine these options.

PRINT=number

The number of statements to include in the output. included, TKPROF will list all statements in the output.

EXPLAIN=username/password@sid

Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option will create a PLAN_TABLE of its own, so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished, this table is dropped.

INSERT= filename

This option creates a script to create a table and store the TRACE file statistics for each SQL statement Traced.

RECORD= filename

This option will produce a file of all the user’s SQL statements.

SYS= YES/NO

This option allows the user to request the recursive SQL statements not be displayed in the output. The default is set to YES.

Sort – It will be useful if we want to see the top SQL which are consumed the most resources. Resource like CPU usage, disk usage etc.

The following are the data elements available for sorting:

. prscnt – The number of times the SQL was parsed.

  • prscpu – The CPU time spent parsing.
  • prsela – The elapsed time spent parsing the SQL.
  • prsdsk – The number of physical reads required for the parse.
  • prsmis – The number of consistent block reads required for the parse.
  • prscu – The number of current block reads required for the parse.
  • execnt – The number of times the SQL statement was executed.
  • execpu – The CPU time spent executing the SQL.
  • exeela – The elapsed time spent executing the SQL.
  • exedsk – The number of physical reads during execution.
  • exeqry – The number of consistent block reads during execution.
  • execu – The number of current block reads during execution.
  • exerow – The number of rows processed during execution.
  • exemis – The number of library cache misses during execution.
  • fchcnt – The number of fetches performed.
  • fchcpu – The CPU time spent fetching rows.
  • fchela – The elapsed time spent fetching rows.
  • fchdsk – The number of physical disk reads during the fetch.
  • fchqry – The number of consistent block reads during the fetch.
  • fchcu – The number of current block reads during the fetch.
  • fchrow – The number of rows fetched for the query.

NOTE:

The TKPROF utility puts a TRACED output into a readable format. Without running TKPROF, it would be difficult to read the output of a TRACE. By specifying “explain=username/password” (noted earlier), we are able to get the EXPLAIN PLAN execution path in addition to the execution statistics of the query

More on Tkprof :  http://www.oracleutilities.com/OSUtil/tkprof.html

Steps To Generate AWR Report

Step 1: Go to $ORACLE_HOME.

Step 2: Once in $ORACLE_HOME go to cd rdbms and then cd admin.

Step 3: Run command ‘ls –al awr*’.

Step 4: Go to ‘sqlplus /nolog’.

Step 5: In sqlplus the following is run ‘SQL> @awrrpt.sql. Select the format for the
report as either ‘HTML’ or ‘TEXT’.

Step 6: Select number of days you want to go back or just hit enter for listing all
completed snapshots.

Step 7: Then specify Begin and End snapshot Ids.

Step 8: Here you specify the name of the report or select the default name assigned.

Step 9: The report gets generated.

Step 10: Exit SQLPLUS.

Step 11: Run command ls –ltr to show the new file created under the path:
/d01/oratst/nbarney/product/10.2/rdbms/admin

Oracle Statspack

Statspack is a set of performance monitoring and reporting utilities provided by Oracle starting from Oracle 8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.

Oracle’s Statspack replaces utlbstat.sql and utlestat.sql.

Some of the other statspack scripts are:

  • sppurge.sql– Purge (delete) a range of Snapshot Id’s between the specified begin and end Snap Id’s
  • spauto.sql– Schedule a dbms_jobto automate the collection of STATPACK statistics
  • spcreate.sql– Installs the STATSPACK user, tables and package on a database (Run as SYS).
  • spdrop.sql– Deinstall STATSPACK from database (Run as SYS)
  • spreport.sql– Report on differences between values recorded in two snapshots
  • sptrunc.sql– Truncates all data in Statspack tables

Perfstat user has the following tables that can be queried in order to get information about snapshots:

  • STATS$BG_EVENT_SUMMARY
  • STATS$BUFFER_POOL_STATISTICS
  • STATS$DATABASE_INSTANCE
  • STATS$ENQUEUESTAT
  • STATS$FILESTATXS
  • STATS$IDLE_EVENT
  • STATS$LATCH
  • STATS$LATCH_CHILDREN
  • STATS$LATCH_MISSES_SUMMARY
  • STATS$LATCH_PARENT
  • STATS$LEVEL_DESCRIPTION
  • STATS$LIBRARYCACHE
  • STATS$PARAMETER
  • STATS$ROLLSTAT
  • STATS$ROWCACHE_SUMMARY
  • STATS$SESSION_EVENT
  • STATS$SESSTAT
  • STATS$SGA
  • STATS$SGASTAT
  • STATS$SNAPSHOT
  • STATS$SQLTEXT
  • STATS$SQL_STATISTICS
  • STATS$SQL_SUMMARY
  • STATS$STATSPACK_PARAMETER
  • STATS$SYSSTAT
  • STATS$SYSTEM_EVENT
  • STATS$TEMPSTATXS
  • STATS$WAITSTAT

Installation :

The Statspack package shouldn’t be installed on the SYSTEM tablespace. Use a separate  tablespace for it using the default initial and next extent size of 1 Mbytes. The minimum default space requirement is approximately 60 Mbytes, however the amount of space required is difficult to estimate because it is based on the amount of data collected, the size of the database and also the instance. You should create the temporary and permanent tablespace to install the StatsPack before because if you install it on interactive mode, you will be prompted to input those tablespaces (the tablespaces that will be used by PERFSTAT user).

Altering the default statistic level

When taking a snapshot, a statistic level can be given that determines the amount of statistics gathered. The higher the statistic level, the more data is collected.

The following descriptions were extracted using

select * from perfstat.stats$level_description:

  • 0: This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information
  • 5: This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels
  • 6: This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels
  • 7: This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels
  • 10: This level includes capturing Child Latch statistics, along with all data captured by lower levels

Additionally, level 5 and level 10 captures sql statement that exceed any of the following adjustable thresholds:

  • The number of executions of the SQL statement (adjustable with the i_executions_th parameter)
  • The number of disk reads the SQL statement performs (adjustable with the i_disk_reads_th parameter)
  • The number of parse calls the SQL statement performs (adjustable with the i_parse_calls_th parameter)
  • The number of buffer gets the SQL statement performs (adjustable with the i_buffer_gets_th parameter)
Table A-1 Levels of Statistics
Level Information Collected
0 General Performance Statistics
5 Addition Data: SQL Statements
6 Addition Data: SQL Plans and SQL Plan Usage
7 Addition Data: Segment Level Statistics
10 Addition Data: Parent and Child Latches

Defaults are stored in stats$statspack_parameters and can be changed with  statspack.modify_statspack_parameter.

The statistic level is passed with the i_snap_level parameter:

begin

  statspack.snap(i_snap_level=> 10);

end;

/

Installation of the Oracle Statspack tool is a relatively simple process. The following is a step-by-step guide to the process of installing Oracle Statspack on a UNIX system.

STEP 1:  Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

STEP 2:  Start the Statspack install script, spcreate.sql, as follows:

sqlplus “/ as sysdba” @spcreate.sql

STEP 3:  Enter a password for the PERFSTAT user when prompted.

STEP 4:  Enter the default tablespace (tools) for the PERFSTAT user when prompted.

STEP 5:  Enter the temporary tablespace (temp) for the PERFSTAT user when prompted.

To run a Statspack report.

Step 1:  Navigate to the $ORACLE_HOME/rdbms/admin directory as follows:

# cd $ORACLE_HOME/rdbms/admin/

Step 2: Run the standard Statspack report as follows:

# sqlplus perfstat/<password> @spreport

  • Enter a beginning snapshot ID.
  • Enter an ending snapshot ID.
  • Enter a name for the report or accept the default.

Step 3:  View the report using more/vi

     THANK YOU

DB Monitoring & Performance Script

The Monitoring of predefined events that generates a message or warning when a certain threshold has been exceeded. This is done in an effort to ensure that an issue doesn’t become a problem. The database monitoring is required for the following reason:

  • – Smooth running of production
  • – Keeping an eye on development
  • – Database performance
  • – In Support of an SLA (service level agreement)

Types of DB Monitoring

  1. Status
  2. Performance
  3. Trend Analysis

Status Monitoring:

Monitor the current status of an event and reports when it exceeds a defined threshold.

Database:

  • – Database/Listener
  • – Monitor Alert. log Message on regular basis.
  • – Check all last night backup is successful.
  • – Tablespace/Datafiles full or Fragmented.
  • – Identify bad growth of segment.
  • – Identify at least 1 top resource consuming query
  • – Monitor Locking
  • – Check Maximum Extent about to be reached.
  • – Redo log Tracking
  • – UNDO and Temp Segment Free space.
  • – Monitor Running Job
  • – Tracking DB User/Session Information.
  • – Important Object Information

OS:

  • – SGA/PGA information
  • – CPU Usage Information
  • – Memory Utilization
  • – Disk Utilization

Performance Monitoring:

Monitor a defined set of performance statistics. This is done in an effort to maintain the best possible DB performance.

Trend Analysis Monitoring:

Collect the historical data for specified events and analyze these data on schedule basis to reveal any potential problems. For Example watching growth of data in a tablespace and predicting when it will fill.
Apart from the above checklist some of the other checklist a DBA are using. It is depend on the requirement. I am mentioning here some of the related query and scripts. It is fully related to DB Monitoring Purpose.
Note: Keep every one informed specially your senior or Junior DBA, System Admin, Manager and do not forget to document very important update.

Database Information:

******************************************************************************************************************************************************************
Track OS Reboot Time:
net statistics server
systeminfo | find “Up Time”  — to find system last uptime
systeminfo | find “System Boot Time”  — to find system boot time
net statistics workstation | find “Statistics” Workstation Statistics for \\A5541TAG-WKS   –perticular workstation statistics
Database and Instance Last start time:
SELECT to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) “DB Startup Time”
FROM   sys.v_$instance;
SELECT SYSDATE-logon_time “Days”, (SYSDATE-logon_time)*24 “Hours”
from  sys.v_$session where  sid=1;
Track Database Version:
SELECT * from v$version;
Track Database Name and ID information:
SELECT DBID, NAME FROM V$DATABASE;‎
Track Database Global Name information:
SELECT * FROM GLOBAL_NAME;‎
Track Database Instance name:
SELECT INSTANCE_NAME FROM V$INSTANCE;‎
Track Database Host Details:
SELECT UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;
Display information about database services
SELECT name,  network_name FROM   dba_services ORDER BY name;

Track Database Present Status:

SELECT created, RESETLOGS_TIME, Log_mode FROM V$DATABASE;
DB Character Set Information:
Select * from nls_database_parameters;
Track Database default information:
Select username, profile, default_tablespace, temporary_tablespace from dba_users;
Track Total Size of Database:
select a.data_size+b.temp_size+c.redo_size “Total_Size (GB)”
from ( select sum(bytes/1024/1024/1024) data_size
from dba_data_files ) a, ( select nvl(sum(bytes/1024/1024/1024),0) temp_size
from dba_temp_files ) b, ( select sum(bytes/1024/1024/1024) redo_size
from sys.v_$log ) c;
Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ‘ GB’ “Database Size”,round(free.p / 1024 / 1024/1024) || ‘ GB’ “Free space”
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;
Track Database Structure:
select name from   sys.v_$controlfile;
select group#,member from   sys.v_$logfile;
Select F.file_id Id, F.file_name name, F.bytes/(1024*1024) Mbyte,
decode(F.status,’AVAILABLE’,’OK’,F.status) status, F.tablespace_name Tspace
from   sys.dba_data_files F
order by tablespace_name;
Tablespace/Datafile/Temp/UNDO Information:
******************************************************************************************************************************************************************
Track Tablespace Used/Free Space:
SELECT /* + RULE */  df.tablespace_name “Tablespace”,  df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”, Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”, Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Track all Tablespaces with free space < 10%
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) – (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
Track Tablespace Fragmentation Details:
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from  ( select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files
group by tablespace_name) a  group by a.tablespace_name
order by pct_free;
Track Non-Sys owned tables in SYSTEM Tablespace:
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = ‘SYSTEM’ AND owner NOT IN (‘SYSTEM’, ‘SYS’, ‘OUTLN’);
Track Default and Temporary Tablespace:
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like ‘%DEFAULT%’;
select username,temporary_tablespace,default_tablespace from dba_users where username=’HRMS’;  –for Particular User
Select default_tablespace,temporary_tablespace,username from dba_users;   –for All Users
Track DB datafile used and free space:
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) –  NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
Track Datafile with Archive Details:
SELECT NAME, a.status, DECODE (b.status, ‘Active’, ‘Backup’, ‘Normal’) arc, enabled, bytes, change#, TIME ARCHIVE FROM sys.v_$datafile a, sys.v_$backup b WHERE a.file# = b.file#;
Track Datafiles with highest I/O activity:
Select * from (select name,phyrds, phywrts,readtim,writetim
from v$filestat a, v$datafile b
where a.file#=b.file#
order by readtim desc) where rownum <6;
Track Datafile as per the Physical Read/Write Percentage:
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT   NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct, phywrts, phywrts * 100 / trw.phys_wrts write_pct FROM totreadwrite trw, v$datafile df, v$filestat fs WHERE df.file# = fs.file# ORDER BY phyrds DESC;
Checking  Autoextend ON/OFF for Datafile:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
‎select tablespace_name,AUTOEXTENSIBLE from dba_data_files;
More on Tablespace/Datafile size click on the link: DB Tablespace/Datafile Details
Temp Segment:
Track Temp Segment Free space:
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM   V$temp_space_header
GROUP  BY tablespace_name;
SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM  v$sort_segment A, (SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Track Who is Currently using the Temp:
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||’M’ “SIZE”,
a.sid||’,’||a.serial# SID_SERIAL, a.username, a.program
FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p
WHERE p.name  = ‘db_block_size’ AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Undo & Rollback Segment:
Monitor UNDO information:
select to_char(begin_time,’hh24:mi:ss’),to_char(end_time,’hh24:mi:ss’), maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks;
Track Active Rollback Segment:
SELECT   r.NAME, l.sid, p.spid, NVL (p.username, ‘no transaction’) “Transaction”,
p.terminal “Terminal” FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+) AND TRUNC (l.id1(+) / 65536) = r.usn AND l.TYPE(+) = ‘TX’ AND l.lmode(+) = 6 ORDER BY R.NAME;
Track Currently Who is using UNDO and TEMP:
SELECT TO_CHAR(s.sid)||’,’||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, ‘None’) orauser, s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||’K’ “Undo”
FROM sys.v_$rollname    r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter   x
WHERE s.taddr = t.addr AND r.usn   = t.xidusn(+) AND x.name  = ‘db_block_size’;
Redolog Information:
******************************************************************************************************************************************************************
Track Redo Generation by Calender Year:
select to_char(first_time,’mm.DD.rrrr’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’99’) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’99’) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’99’) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’99’) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’99’) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’99’) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’99’) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’99’) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’99’) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’99’) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’99’) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’99’) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
from v$log_history group by to_char(first_time,’mm.DD.rrrr’)
order by day;
Track Redo generation by day:
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) “REDO PER DAY (MB)” from v$archived_log
group by trunc(completion_time) order by 1;
Track How much full is the current redo log file:
SELECT le.leseq   “Current log sequence No”, 100*cp.cpodr_bno/le.lesiz “Percent Full”,
cp.cpodr_bno   “Current Block No”, le.lesiz   “Size of Log in Blocks”
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
Monitor Running Jobs:
******************************************************************************************************************************************************************
Long Jobs:
Select username,to_char(start_time, ‘hh24:mi:ss dd/mm/yy’) started, time_remaining remaining, message
from v$session_longops
where time_remaining = 0 order by time_remaining desc;
Monitor Long running Job:
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1;
Track Long Query Progress in database:
SELECT a.sid, a.serial#, b.username , opname OPERATION, target OBJECT,
TRUNC(elapsed_seconds, 5) “ET (s)”, TO_CHAR(start_time, ‘HH24:MI:SS’) start_time,
ROUND((sofar/totalwork)*100, 2) “COMPLETE (%)”
FROM v$session_longops a, v$session b
WHERE a.sid = b.sid AND b.username not IN (‘SYS’, ‘SYSTEM’) AND totalwork > 0
ORDER BY elapsed_seconds;
Track Running RMAN backup status:
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’  AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0 AND SOFAR  != TOTALWORK;
Monitor Import Rate:
Oracle Import Utility usually takes hours for very large tables and we need to track the execution of Oracle Import Process. Below option can help you monitor the rate at which rows are being imported from a running import job.
select   substr(sql_text,instr(sql_text,’into “‘),30) table_name,
rows_processed, round((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,’yyyy-mm-dd hh24:mi:ss’))*24*60)) rows_per_minute
from   sys.v_$sqlarea
where   sql_text like ‘insert %into “%’ and command_type = 2 and open_versions > 0;
Displays SQL statements for the current database sessions.
SELECT s.sid,  s.status,  s.process,   s.schemaname, s.osuser, a.sql_text,  p.program
FROM v$session s, v$sqlarea a, v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE

AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR;
Displays SQL statements that are using the most resources.
SELECT * FROM   (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address
FROM   v$sqlarea a
ORDER BY 2 DESC)

WHERE  rownum <= &&1;
Database SGA Report:
******************************************************************************************************************************************************************
Monitor SGA Information:
SELECT SUM(VALUE)/1024/1024 “Size in MB” from SYS.v_$sga;
select     NAME,   BYTES from     v$sgastat  order by NAME;
Monitor Shared Pool Information:
select to_number(value) shared_pool_size, sum_obj_size, sum_sql_size, sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type <> ‘CURSOR’),
(select sum(sharable_mem) sum_sql_size from v$sqlarea),
(select sum(250 * users_opening) sum_user_size from v$sqlarea), v$parameter
where name = ‘shared_pool_size’;
Monitor PGA Information:
Select st.sid “SID”, sn.name “TYPE”, ceil(st.value / 1024 / 1024/1024) “GB”
from v$sesstat st, v$statname sn where st.statistic# = sn.statistic#
and sid in (select sid from v$session where username like UPPER(‘hrms’))
and upper(sn.name) like ‘%PGA%’ order by st.sid, st.value desc;
Monitor CPU Usage Information:
select  ss.username, se.SID, VALUE/100 cpu_usage_seconds
from v$session ss,  v$sesstat se,  v$statname sn where se.STATISTIC# = sn.STATISTIC#
and NAME like ‘%CPU used by this session%’ and se.SID = ss.SID
and  ss.status=’ACTIVE’ and  ss.username is not null order by VALUE desc;
Disk I/O Report:
WITH totreadwrite AS (SELECT SUM (phyrds) phys_reads, SUM (phywrts) phys_wrts FROM v$filestat)
SELECT   NAME, phyrds, phyrds * 100 / trw.phys_reads read_pct,
phywrts, phywrts * 100 / trw.phys_wrts write_pct
FROM totreadwrite trw, v$datafile df, v$filestat fs
WHERE df.file# = fs.file# ORDER BY phyrds DESC;
IO Usage for a Query:
select b.sql_text “Statement “, a.Disk_reads “Disk Reads”, a.executions “Executions”,
a.disk_reads/decode(a.executions,0,1,a.executions) “Ratio”,c.username
from  v$sqlarea a, v$sqltext_with_newlines b,dba_users c
where  a.parsing_user_id = c.user_id and a.address=b.address and a.disk_reads>100000
order by a.disk_reads desc,b.piece;
Display the System write batch size:
SELECT kviival write_batch_size
FROM x$kvii
WHERE kviidsc = ‘DB writer IO clump’ OR kviitag = ‘kcbswc’
Monitor Disk I/O Contention:
select   NAME,  PHYRDS “Physical Reads”,
round((PHYRDS / PD.PHYS_READS)*100,2) “Read %”,   PHYWRTS “Physical Writes”,
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) “Write %”,   fs.PHYBLKRD+FS.PHYBLKWRT “Total Block I/O’s” from (    select     sum(PHYRDS) PHYS_READS, sum(PHYWRTS) PHYS_WRTS
from    v$filestat    ) pd,  v$datafile df,  v$filestat fs
where     df.FILE# = fs.FILE#
order     by fs.PHYBLKRD+fs.PHYBLKWRT desc;
For information about database latch statistics and wait information. Click on the below link: Latch Statistics & Wait information
DB Locks/Blocks/Blocker Details:
******************************************************************************************************************************************************************
Track Block session in oracle 9i/10g  
‎select s1.username || ‘@’ || s1.machine || ‘ ( SID=’ || s1.sid ||  ‘ )  is blocking ‘ || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ ||  s2.sid || ‘ ) ‘ AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid = l1.sid and s2.sid = l2.sid  and l1.BLOCK = 1  and l2.request > 0  and l1.id1 = l2.id1  and l2.id2 = l2.id2;
select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from gv$session s, dba_objects do
where sid = 543 and s.ROW_WAIT_OBJ# = do.OBJECT_ID;
For detail description of blocking you can run this on your Oracle-Home
oracle-home\rdbms\admin\utllockt.sql
Select process,sid, blocking_session from v$session where blocking_session is not null;  –in 10g
Track Locked Session & Blocked:
PROMPT Blocked and Blocker Sessions
select /*+ ORDERED */ blocker.sid blocker_sid, blocked.sid blocked_sid ,
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select *from v$lock
where block != 0 and type = ‘TX’) blocker, v$lock blocked
where blocked.type=’TX’ and blocked.block = 0 and blocked.id1 = blocker.id1;
Track Database Lock:
Select /*+ ORDERED */ l.sid, l.lmode,
TRUNC(l.ctime/60) min_blocked, u.name||’.’||o.NAME blocked_obj
from (select * from v$lock
where type=’TM’ and sid in (select sid
from v$lock where block!=0)) l, sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;
Track the Session Waiting for Lock:
SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type TYPE, mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.holding_session = b.sid AND c.waiting_session = a.sid;
Track Blocker Details:
SELECT sid, serial#, username, osuser, machine
FROM v$session
WHERE sid IN (select sid from v$lock
where block != 0 and type = ‘TX’);
Users/Sessions/Processes Details:
******************************************************************************************************************************************************************
Average Wait Time for Particular Event:
SELECT EVENT,  TOTAL_WAITS,  TOTAL_TIMEOUTS,  TIME_WAITED, round(AVERAGE_WAIT,2) “Average Wait”
from v$system_event order    by TOTAL_WAITS;
Sessions Waiting On A Particular Wait Event:
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0 AND event NOT IN (‘smon timer’,’pipe get’,’wakeup time manager’, ‘pmon timer’,’rdbms ipc message’, ‘SQL*Net message from client’)
GROUP BY event ORDER BY 1 DESC;
Track Logon time of DB user and OS user:
Select to_char(logon_time,’dd/mm/yyyy hh24:mi:ss’),osuser,status,schemaname,machine from v$session where type !=’BACKGROUND’; ‎
Track all Session User Details:
select sid, serial#,machine, status, osuser,username from v$session where username!=’NULL’;
Track Active Session User Details:
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status= ‘ACTIVE’ AND UserName IS NOT NULL;
Track Active User Details:
SELECT s.inst_id,  s.sid,  s.serial#,  p.spid,  s.username,  s.program FROM gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != ‘BACKGROUND’;
Report OS Process ID for each session:
SELECT    ses.username  || ‘(‘  || ses.sid  || ‘)’ users, acc.owner owner, acc.OBJECT OBJECT, ses.lockwait, prc.spid os_process
FROM v$process prc, v$access acc, v$session ses
WHERE prc.addr = ses.paddr AND ses.sid = acc.sid;
Show Username and SID/SPID with Program Name:
select sid,name,value from v$spparameter where isspecified=’TRUE’;‎
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session
WHERE Status= ‘ACTIVE’ AND UserName IS NOT NULL;  –to find active session
SELECT s.inst_id,  s.sid,  s.serial#,  p.spid,  s.username,  s.program    –active users details
FROM gv$session s  JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;
Track Current Transaction in Database:
‎‎select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk  from v$session a, v$transaction b
where a.saddr = b.ses_addr;‎
Important Object Information:
******************************************************************************************************************************************************************
Database Object Information:
Select owner,object_type,count(*) from dba_objects Where owner not IN (‘SYS’,’MDSYS’,’CTXSYS’,’HR’,’ORDSYS’,’OE’,’ODM_MTR’,’WMSYS’,’XDB’,’QS_WS’, ‘RMAN’,’SCOTT’,’QS_ADM’,’QS_CBADM’, ‘ORDSYS’,’OUTLN’,’PM’,’QS_OS’,’QS_ES’,’ODM’,’OLAPSYS’,’WKSYS’,’SH’,’SYSTEM’,’ORDPLUGINS’,’QS’,’QS_CS’)
Group by owner,object_type order by owner;
Query to Find 5 largest object in Database:
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
Track Last DDL Performed in database:
Select CREATED, TIMESTAMP, last_ddl_time from all_objects WHERE OWNER=’HRMS’ AND OBJECT_TYPE=’TABLE’ order by timestamp desc;
Count Invalid Object:
Select owner, object_type, count(*) from dba_objects where status=’INVALID’ group by  owner, object_type;
Report all Invalid Object in Database:
SELECT owner, object_name, object_type,‎ TO_CHAR (last_ddl_time, ‘DD-MON-YY hh:mi:ss’) last_time FROM dba_objects‎ WHERE status = ‘INVALID’;
Report Invalid Object with Next Action:
select ‘Alter ‘ || decode(object_type,’PACKAGE BODY’,’PACKAGE’,object_type) || ‘ ‘ || object_name || ‘ compile ‘ || decode(object_type,’PACKAGE BODY’,’ body;’,’;’) from user_objects where object_type in (‘FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’TRIGGER’,’VIEW’) and status = ‘INVALID’ order by object_type , object_name;
Click on the link to Report Invalid object and How to Compile themReport All Invalid Objects

Track Total Number of Table/Index/Mviews:

Select count(1) from user_tables where table_name not like ‘%$%’
Select count(1) from user_mviews;
Select count(1) from user_indexes where index_type in (‘FUNCTION-BASED NORMAL’,’NORMAL’);
Number of Objects Created in last week:
Select count(1) from user_objects where CREATED >= sysdate – 7
Track Mviews Not Refreshed since last Week:
Select mview_name from user_mviews where LAST_REFRESH_DATE < sysdate – 7;