Database Scripts
How to Find Database Object and it’s Corresponding Datafiles
SELECT DISTINCT t.ts#,
t.name AS Tablespace_name,
d.name AS DATAFILE,
s.owner,
s.segment_name,
s.segment_type,
s.bytes / 1024 / 1024 AS “MB”
FROM v$tablespace t,
v$datafile d,
dba_segments s
WHERE t.ts# = d.ts#
AND t.name = s.tablespace_name
AND s.owner = ‘APPS’;
How to Find CPU Usage for a Session
SET linesize 145
SET pagesize 9999
COLUMN sid format 9999 heading ‘SID’
COLUMN serial_id format 999999 heading ‘Serial#’
COLUMN session_status format a9 heading ‘Status’ justify right
Generating AWR Reports
You can generate AWR reports by running SQL scripts, as described below :-
# Generating an AWR Report
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
# To generate an AWR report on a specific database instance:
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
# To generate an AWR report in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql
#To generate an AWR report on a specific database instance in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql
# To generate an AWR report for a particular SQL statement:
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
# To generate an AWR report for a particular SQL statement on a specified database instance:
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
#To generate an AWR Compare Periods report:
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
# To generate an AWR Compare Periods report in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql
# To generate an AWR Compare Periods report on a specified database instance:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
# To generate an AWR Compare Periods report on a specified database instance in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/awrgdrpi.sql
# To generate an ASH report:
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
# To generate an ASH report on a specified database instance:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
# To generate an ASH report in an Oracle RAC environment:
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
How To Move Table to Different TableSpace
SYNTAX
ALTER TABLE table_name move TABLESPACE tablespace_name (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED);
EXAMPLE
ALTER TABLE hr.employee_details move TABLESPACE hr;
OR
ALTER TABLE hr.employee_details move TABLESPACE hr (INITIAL=64k MINEXTENTS=1 MAXEXTENTS=UNLIMITED);