Day 2 day queries
To Find DB/Instance Name in RAC
=========================
sql> show parameter cluster;
col host_name for a20
select inst_id,instance_name,host_name,instance_role from gv$instance;
select instance_name from v$instance;
To check from the operating system
# lsnodes
To get the NLS PARAMETER details
================================
set linesize 150
set pagesize 1000
col name for a50
col parameter for a30
col value for a30
col member for a50
col DATAFILE_NAME for a70
col TEMPFILE_NAME for a70
col CONTROLFILE_NAME for a70
col LOGFILE_MEMBER for a70
select * from NLS_DATABASE_PARAMETERS;
To check the Instance Uptime
================================
select instance_name||', up since '|| to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') start_time from gv$instance;
To Identify Space occupied and original space utilized of a database
=====================================================================
set numf 999999.99
select sum(bytes)/(1024*1024*1024) "Total Allocated GB",Total_Consumed_GB from dba_data_files,
(select sum(bytes)/(1024*1024*1024) Total_Consumed_GB from dba_segments) group by Total_Consumed_GB;
To get the DATABASE SCHEMA SIZE from the database
===================================================
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;
To get the LOGFILES INFORMATION from the database
==================================================
col member for a65
select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 "BYTES IN MB",ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#
To get the CONTROLFILES INFORMATION from the database
=======================================================
col name for a90
select * from v$controlfile
To get the TEMPFILES SIZE from the database
=============================================
select tablespace_name,file_name,bytes/1024/1024 "SIZE_IN_MB" from dba_temp_files order by file_name asc;
To get the TABLESPACE SIZE from the database
==============================================
select tablespace_name,sum(bytes)/1024/1024 "SIZE_IN_MB" from dba_data_files group by tablespace_name;
To get the datafile sizes from the database
================================================
col file_name for a70
col tablespace_name for a30
clear breaks
clear computes
compute sum of SIZE_IN_MB on report
break on report
select tablespace_name,file_name,AUTOEXTENSIBLE,INCREMENT_BY,MAXBYTES/1024/1024 "MAX in MB",bytes/1024/1024 "SIZE_IN_MB" from dba_data_files order by tablespace_name;
To get information about the database
======================================
SET LINESIZE 150
SET PAGESIZE 50000
cOL HOST_NAME FOR A25
col LOGINS FOR A20
col STATUS for A15
col "STARTUP_TIME" FOR A30
col INSTANCE_NAME for a20
col VERSION for a20
select INSTANCE_NAME,HOST_NAME,VERSION,LOGINS,STATUS,to_char(STARTUP_TIME,'DD-MON-YYYY DAY HH24:MI:SS')
"STARTUP_TIME" FROM v$instance;
col name for a30
col "CREATED" for a25
col LOG_MODE for a15
col OPEN_MODE for a15
col DATABASE_ROLE for a15
select NAME,to_char(CREATED,'DD-MON-YYYY HH24:MI:SS') "CREATED",LOG_MODE,OPEN_MODE,DATABASE_ROLE from v$database;
############33Procedure for generating the DDL statements of the database objects.
=========================================================================
There are two parameters used in the specified object,
1. Object Type ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,
2. Directory Name ==> Specify the directory name where the output to be stored.
Usage - DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)
Objects to be created:
----------------------
CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE"
( "COMNAME" VARCHAR2(200 BYTE),
"COMDDDL" LONG
);
Create or Replace
PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)
AS
V_DDL LONG;
V_ERRMSG VARCHAR2(500);
vInHandle utl_file.file_type;
CURSOR C1 IS
SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;
BEGIN
FOR I IN C1 LOOP
select
dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME) INTO V_DDL
from dual;
vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');
IF utl_file.is_open(vInHandle) THEN
utl_file.put_line(vInHandle, V_DDL, FALSE);
utl_file.fflush(vInHandle);
utl_file.fclose_all;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
V_ERRMSG:=SQLERRM;
INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);
END;
-- +==================================================================+
-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type
-- +==================================================================+
SET LINESIZE 155
SET PAGESIZE 9999
SET VERIFY OFF
BREAK ON segment_type SKIP 1
COMPUTE SUM OF bytes ON segment_type
COLUMN segment_type FORMAT A20 HEADING 'Segment Type'
COLUMN owner FORMAT A15 HEADING 'Owner'
COLUMN segment_name FORMAT A30 HEADING 'Segment Name'
COLUMN partition_name FORMAT A30 HEADING 'Partition Name'
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'
COLUMN bytes FORMAT 9,999,999,999,999 HEADING 'Size (in bytes)'
COLUMN extents FORMAT 999,999,999 HEADING 'Extents'
SELECT
a.segment_type segment_type
, a.owner owner
, a.segment_name segment_name
, a.partition_name partition_name
, a.tablespace_name tablespace_name
, a.bytes bytes
, a.extents extents
FROM
(select
b.segment_type
, b.owner
, b.segment_name
, b.partition_name
, b.tablespace_name
, b.bytes
, b.extents
from
dba_segments b
order by
b.bytes desc
) a
WHERE
rownum < 101
ORDER BY
segment_type, bytes desc, owner, segment_name
/
To check for index fragmentation
-- +------------------------------------------------------------------------------+
-- | PURPOSE : To check for index fragmentation. As a rule of thumb if 10-15% |
-- | of the table data changes, then you should consider rebuilding the index |
-- +-------------------------------------------------------------------------------+
ANALYZE INDEX &&index_name VALIDATE STRUCTURE;
COL name HEADING 'Index Name' FORMAT a30
COL del_lf_rows HEADING 'Deleted|Leaf Rows' FORMAT 99999999
COL lf_rows_used HEADING 'Used|Leaf Rows' FORMAT 99999999
COL ibadness HEADING '% Deleted|Leaf Rows' FORMAT 999.99999
SELECT
name
, del_lf_rows
, lf_rows - del_lf_rows lf_rows_used
, TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness
FROM index_stats
/
prompt
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt
undefine index_name
Report free space fragmentation
-- +----------------------------------------------------------------------------+
-- | PURPOSE : Report free space fragmentation. |
-- | THIS SCRIPT MUST BE RUN AS THE SYS USER!!! |
-- +----------------------------------------------------------------------------+
connect / as sysdba
CREATE OR REPLACE VIEW free_space (
tablespace
, pieces
, free_bytes
, free_blocks
, largest_bytes
, largest_blks
, fsfi
, data_file
, file_id
, total_blocks
)
AS
SELECT
a.tablespace_name
, COUNT(*)
, SUM(a.bytes)
, SUM(a.blocks)
, MAX(a.bytes)
, MAX(a.blocks)
, SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))
, UPPER(b.file_name)
, MAX(a.file_id)
, MAX(b.blocks)
FROM
sys.dba_free_space a
, sys.dba_data_files b
WHERE
a.file_id = b.file_id
GROUP BY
a.tablespace_name, b.file_name
/
CLEAR COLUMNS
SET LINESIZE 120
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY off
BREAK ON tablespace SKIP 2 ON REPORT
COMPUTE SUM OF total_blocks ON tablespace
COMPUTE SUM OF free_blocks ON tablespace
COMPUTE SUM OF free_blocks ON report
COMPUTE SUM OF total_blocks ON report
COLUMN tablespace HEADING "Tablespace" FORMAT a15
COLUMN file_id HEADING File# FORMAT 99999
COLUMN pieces HEADING Frag FORMAT 9999
COLUMN free_bytes HEADING 'Free Byte'
COLUMN free_blocks HEADING 'Free Blk' FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest Bytes'
COLUMN largest_blks HEADING 'Biggest Blks' FORMAT 999,999,999
COLUMN data_file HEADING 'File Name' FORMAT a45
COLUMN total_blocks HEADING 'Total Blocks' FORMAT 999,999,999
SELECT
tablespace
, data_file
, pieces
, free_blocks
, largest_blks
, file_id
, total_blocks
FROM
free_space
/
DROP VIEW free_space
/
List all currently connected user sessions ordered by current PGA size
-- +-------------------------------------------------------------------------------------+
-- | PURPOSE : List all currently connected user sessions ordered by current PGA size |
-- +-------------------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Current PGA Size |
prompt +----------------------------------------------------+
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,8) session_machine
, sstat1.value session_pga_memory
, sstat2.value session_pga_memory_max
, sstat3.value session_uga_memory
, sstat4.value session_uga_memory_max
FROM
v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat1.sid
AND s.sid = sstat2.sid
AND s.sid = sstat3.sid
AND s.sid = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name = 'session pga memory'
AND statname2.name = 'session pga memory max'
AND statname3.name = 'session uga memory'
AND statname4.name = 'session uga memory max'
ORDER BY session_pga_memory DESC
/
List all currently connected user sessions ordered by Logical I/O
-- +--------------------------------------------------------------------------------+
-- | PURPOSE : List all currently connected user sessions ordered by Logical - I/O |
-- +--------------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN logical_io FORMAT 999,999,999,999 HEADING 'Logical I/O'
COLUMN physical_reads FORMAT 999,999,999,999 HEADING 'Physical Reads'
COLUMN physical_writes FORMAT 999,999,999,999 HEADING 'Physical Writes'
prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Logical I/O |
prompt +----------------------------------------------------+
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,8) session_machine
, sstat1.value
+ sstat2.value logical_io
, sstat3.value physical_reads
, sstat4.value physical_writes
FROM
v$process p
, v$session s
, v$sesstat sstat1
, v$sesstat sstat2
, v$sesstat sstat3
, v$sesstat sstat4
, v$statname statname1
, v$statname statname2
, v$statname statname3
, v$statname statname4
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat1.sid
AND s.sid = sstat2.sid
AND s.sid = sstat3.sid
AND s.sid = sstat4.sid
AND statname1.statistic# = sstat1.statistic#
AND statname2.statistic# = sstat2.statistic#
AND statname3.statistic# = sstat3.statistic#
AND statname4.statistic# = sstat4.statistic#
AND statname1.name = 'db block gets'
AND statname2.name = 'consistent gets'
AND statname3.name = 'physical reads'
AND statname4.name = 'physical writes'
ORDER BY logical_io DESC
/
List all currently connected user sessions ordered by CPU time.
-- +----------------------------------------------------------------------------+
-- | DATABASE : Oracle |
-- | PURPOSE : List all currently connected user sessions ordered by CPU time. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'
prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by CPU |
prompt +----------------------------------------------------+
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value cpu_value
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC
/
No comments:
Post a Comment