Contents
-
Some usefull TIPS
- List Users Login in
- List Users Created on the Database
- Check archivelog per day
- To quickly find if you have long running processes taking up your resources look at v$sesson_long_ops :
- undo retention optimal
- query invalid index
- count tables
- Slow queries - Top 10
- Script to Report Frequency of Log Switches
- USER idle time
- USER temp tablespace
- USER informations
- Create user sample
- Active Processes(DBA)
- Kill Session
- Show dead case
- How to see the oldest flashback available?
- List Earliest Flashback Database Time and SCN
- List Flashback Database Restore Points
- Datapump Export Script
- Using the redo log sizing advisor
- How to check if tablespaces are autoextend
- Free space in Auto extensible tablespaces
Some usefull TIPS
List Users Login in
SET TERMOUT OFF
STORE SET save_env.sql REPLACE
SET TERMOUT ON
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
SET PAGESIZE 20
SET PAUSE ON
SET PAUSE ' Press <<RETURN>> for next page...'
COL orauser HEA " Oracle User " FOR a17 TRUNC
COL osuser HEA " O/S User " FOR a10 TRUNC
COL ssid HEA "Sid" FOR a4
COL sserial HEA "Serial#" FOR a7
COL ospid HEA "O/S Pid" FOR a7
COL slogon HEA " Logon Time " FOR a14
COL sstat HEA "Status" FOR a6
COL auth HEA "Auth" FOR a4
COL conn HEA "Con" FOR a3
SELECT
' '||NVL( s.username, ' ???? ' ) orauser,
' '||s.osuser osuser,
LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,
LPAD( p.spid, 6 ) ospid,
INITCAP( LOWER( TO_CHAR( logon_time, 'MONDD HH24:MI:SS' ) ) ) slogon,
DECODE( s.status, 'ACTIVE', ' Busy ', 'INACTIVE', ' Idle ', 'KILLED', ' Kill ', ' ?? ' ) sstat,
DECODE( sc.authentication_type, 'DATABASE', ' DB ', 'OS', ' OS ', ' ?? ' ) auth,
DECODE( s.server, 'DEDICATED', 'Dir', 'NONE', 'Mts', 'SHARED', 'Mts', '???' ) conn
FROM
v$session s, v$process p,
(
SELECT
DISTINCT sid, authentication_type
FROM
v$session_connect_info
) sc
WHERE
s.paddr = p.addr AND s.sid = sc.sid
ORDER BY
s.status,s.sid
/
List Users Created on the Database
--
-- List Database Users
--
SET TERMOUT OFF
STORE SET save_env.sql REPLACE
SET TERMOUT ON
CLEAR BREAKS
CLEAR COLUMNS
CLEAR COMPUTES
COL username HEA " User Name " FOR a21
COL default_tablespace HEA " Default Tablespace " FOR a20;
COL temporary_tablespace HEA "Temporary Tablespace" FOR a20;
COL grole HEA " DBA " FOR a5;
SELECT
LPAD( DECODE( p.granted_role, 'DBA' , '*' ), 3 ) grole,
u.username, u.default_tablespace, u.temporary_tablespace
FROM
dba_users u,
(
SELECT
grantee, granted_role
FROM
dba_role_privs
WHERE
granted_role = 'DBA'
) p
WHERE
u.username = p.grantee (+)
ORDER BY
u.username
/
@save_env.sql
host rm save_env.sql
SET TERMOUT ON
Check archivelog per day
select trunc(completion_time) as "Date",count(*) as "Count" ,((sum(blocks * block_size)) /1024 /1024) as "MB" from v$archived_log group by trunc(completion_time); Select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024)) as mb from v$archived_log group by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;
To quickly find if you have long running processes taking up your resources look at v$sesson_long_ops :
SELECT * FROM v$session_longops;
SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID='XXX';
SELECT
s.username,
sa.sql_text
FROM v$process p
INNER JOIN v$session s ON p.addr=s.paddr
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address
WHERE s.username IS NOT NULL AND p.spid=&SPID
SELECT sql_text FROM v$session s
LEFT JOIN v$sqlarea sa ON s.sql_hash_value=sa.hash_value AND s.sql_address=sa.address
WHERE sid=&sid
undo retention optimal
- 在磁盘空间紧张的情况下，可以调整undo_retention
查询undo大小，undo_retention实际大小和优化大小：
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/- 在磁盘空间空闲的范围内，保证undo_retention不变，调整undo_size
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
query invalid index
select index_name,table_name,tablespace_name,status from dba_indexes where status<>'VALID'; select index_name, partition_name, status from user_ind_partitions; select index_name, status from user_indexes where status<>'VALID'; select * from user_ind_partitions where status != 'USABLE';
- In DBA_INDEXES there are some indexes with status 'N/A'.
N/A indicates that index is a partitioned index.When we create a partitioned index,the corresponding status in DBA_INDEXES is "N/A".
* Sub partition rebuild:
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' parallel 8;' from dbA_ind_subpartitions where status not in ('USABLE','VALID')
* Partition rebuild:
select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' parallel 8;' from dbA_ind_partitions where status not in ('USABLE','VALID')
* Index rebuild:
select 'alter index '||owner||'.'||index_name||' rebuild parallel 8;' from dbA_indexes where status not in ('USABLE','VALID')
http://ermanarslan.blogspot.com/2014/01/is-my-indexindex-subpartition-unusable.html
count tables
declare
v_count integer;
begin
for r in (select table_name, owner from all_tables
where owner = 'SCHEMA_NAME')
loop
execute immediate 'select count(*) from ' || r.table_name
into v_count;
INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
VALUES (r.table_name,r.owner,v_count,SYSDATE);
end loop;
end;select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'OA'
Slow queries - Top 10
This script will return you information about the 10 queries with the highest avarage execution time. -- By ArthurSens
select * from
(
select
sql_id,
elapsed_time,
elapsed_time/executions avg_elapsed,
cpu_time/executions avg_cpu,
sql_text
from v$sql
order by avg_elapsed desc
)
where rownum < 11
Script to Report Frequency of Log Switches
REM LOCATION: Object Management\Redo Logs
REM FUNCTION: Provide data on Redo Log Switches
REM TESTED ON: 10.2.0.3, 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: v$thread
REM
REM This is a part of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C) 2008 Quest Software
REM All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
COLUMN avg_log_switch_min format 9,999.99 heading "Average|Log|Switch|Times|(Min)"
SET lines 132 pages 60 feedback off verify off echo off
TTITLE 'Average Log Switch Time Report'
PROMPT 'Note that high rates of Redo Log switching can cause performance problems'
PROMPT 'If this report indicates average switch times of less than 10 minutes'
PROMPT 'you should consider increasing the size of your redo logs.'
WITH redo_log_switch_times AS
(SELECT sequence#, first_time,
LAG (first_time, 1) OVER (ORDER BY first_time) AS LAG,
first_time
- LAG (first_time, 1) OVER (ORDER BY first_time) lag_time,
1440
* (first_time - LAG (first_time, 1) OVER (ORDER BY first_time)
) lag_time_pct_mins
FROM v$log_history
ORDER BY sequence#)
SELECT AVG (lag_time_pct_mins) avg_log_switch_min
FROM redo_log_switch_times;
USER idle time
SELECT sid, osuser, username, status, TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME, FLOOR(last_call_et/3600)||':'|| FLOOR(MOD(last_call_et,3600)/60)||':'|| MOD(MOD(last_call_et,3600),60) IDLE, program FROM v_$session WHERE username IS NOT NULL ORDER BY last_call_et;
USER temp tablespace
SELECT username, default_tablespace, temporary_tablespace FROM dba_users ORDER BY 1;
USER informations
SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role, r.admin_option, r.default_role FROM sys.dba_users u, sys.dba_role_privs r WHERE u.username = r.grantee (+) GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role, r.admin_option, r.default_role;
Create user sample
CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespacename TEMPORARY SPACE temp_tablespacename QUOTA UNLIMITED ON tablespacename; GRANT CONNECT TO username; GRANT RESOURCE TO username;
Active Processes(DBA)
SET pagesize 55;
SET linesize 170;
col SQL format a80;
col SERVER heading 'SVR' format a3;
col EVENT heading 'WAITING' format a30 fold_after;
col OSUSER heading 'OSUSER' format a8;
col USERNAME heading 'USERNAME' format a8;
col PID heading 'OSPID' format 99999;
col DISK_READS heading 'DISK I/O' format 99999999;
col BUFFER_GETS heading 'BUFFER|GETS' format 99999999;
SELECT SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
V$SESSION.OSUSER OSUSER,
-- DECODE(V$SESSION.SERVER,'DEDICATED','D','SHARED','S','O') SERVER,
V$SQLAREA.DISK_READS DISK_READS,
V$SQLAREA.BUFFER_GETS BUFFER_GETS,
SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
V$SESSION.PROCESS PID,
V$SESSION_WAIT.EVENT EVENT,
V$SQLAREA.SQL_TEXT SQL
FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
V$SESSION.STATUS = 'ACTIVE' AND
V$SESSION_WAIT.EVENT != 'client message'
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;
Kill Session
- Identify the Session to be Killed
SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45 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';
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'LSHR';
The basic syntax for killing a session is shown below. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION '7,15'; In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
Show dead case
SELECT distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
How to see the oldest flashback available?
Using the following query one can see the flashback data available.
SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME, (sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;
List Earliest Flashback Database Time and SCN
-- -- List Earliest Flashback Database Time and SCN. -- SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 SET VERIFY OFF COLUMN oldest_flash_scn FOR 999,999,999 ALTER SESSION SET nls_date_format='DD MON YYYY hh24:mi:ss' / SELECT oldest_flashback_scn, oldest_flashback_time FROM v$flashback_database_log /
List Flashback Database Restore Points
Oracle Database » SQL Library » List Flashback Database Restore Points
--
-- List Flashback Database Restore Points
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
SELECT
database_incarnation# as Incar,
scn,
name,
time,
storage_size,
guarantee_flashback_database
FROM
v$restore_point
ORDER BY 4
/
Datapump Export Script
#
# Title: datapump_backup.ksh
# Purpose: Used to take full datapump exports
# Called by: n/a
#
# Notes: None
#
# Author: Mark ramsay
# Date: 05 April 2012
#
# Notes: Requires an oracle directory to be present called data_pump_dir which points to
# /u01/app/oracle/admin/${ORACLE_SID}/dpdump
# Parameter \$1 should be set to the Oracle SID
# *BE CAREFUL* - The script will remove an dump files from the data_pump_dir
# that match the find command.
#
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
SDS_date=`date +%d%b%Y`
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
expdp \"/ as sysdba\" DUMPFILE=${ORACLE_SID}_full_backup_${SDS_date}.dmp DIRECTORY=data_pump_dir Full=y
cd /u01/app/oracle/admin/${ORACLE_SID}/dpdump
find . -name "${ORACLE_SID}_full_backup_*.dmp" -mtime +14 -follow -exec rm {} \;
Using the redo log sizing advisor
The following SQL will give advice on the optimal size of the redo logs, but manual inspection of redo log switch frequency is always the best approach:
SELECT
(SELECT
ROUND (AVG (BYTES) / 1024 / 1024, 2)
FROM
V$LOG) AS "Redo size (MB)",
ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
(SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS
AVERAGE_PERIOD
FROM
V$ARCHIVED_LOG
WHERE
FIRST_TIME > SYSDATE - 3
AND
TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '16:00' AND '17:00');
How to check if tablespaces are autoextend
select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'TS__';