dba-diary-blog
Humble Diary Of a Database Administrator
2 posts
Don't wanna be here? Send us removal request.
Last Seen Blogs
yliteen
thally
im-the-poison-in-your-mouth
I Feel nothing
x3darkmadnessx3
niebo nie jest limitem.
oktopwork
oktop workroom
audiolibroyoutube
AUDIOLIBRO YOUTUBE
Text
Books to Read
Medicine (Books recommended by Dr. B M Hegde)
Aredicting the unpredictable future - William Ferth - British Medical General 1991 (BEMJ;303:1568)
Against Method Book by Paul Feyerabend
limits of science - Peter Medawar
Molicules of emotions - Candace pert
Death by medicine by gery nul
Is US medicine best in the world - babra starfield JAMA 2000:284:283
Ayurveda
Deepak chopra
1 note
·
View note
Text
Oracle DBA Cheet Sheet
Tablespace & Datafile Details
=============================
set lines 200 pages 200
col tablespace_name for a35
col file_name for a70
select file_id, tablespace_name, file_name, bytes/1024/1024 MB, status from dba_data_files;
Table Analyze Details
=====================
set lines 200 pages 200
col owner for a30
col table_name for a30
col tablespace_name for a35
select owner, table_name, tablespace_name, NUM_ROWS, LAST_ANALYZED from dba_tables where owner='&TableOwner' and table_name='&TableName';
Session Details
===============
set lines 200 pages 200
col MACHINE for a25
select inst_id, sid, serial#, username, program, machine, status from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and username is not null order by 1;
select inst_id, username, count(*) "No_of_Sessions" from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and username is not null and status='INACTIVE' group by inst_id, username order by 3 desc;
select inst_id, username, program, machine, status from gv$session where machine like '%&MachineName%' and username is not null order by 1;
Parameter value
===============
set lines 200 pages 200
col name for a35
col value for a70
select inst_id, name, value from gv$parameter where name like '%&Parameter%' order by inst_id;
User Details
=============
set lines 200 pages 200
col username for a30
col profile for a30
select username, account_status, lock_date, expiry_date, profile from dba_users where username like '%&username%' order by username;
List and Remove Files and directories
=====================================
ls |grep -i cdmp_20110224|xargs rm -r
Tablespace Usage (1)
====================
set pages 999;
set lines 132;
SELECT *
FROM
( SELECT
c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM
( SELECT
tablespace_name,
SUM(a.bytes) bytes
FROM
sys.DBA_DATA_FILES a
GROUP BY
tablespace_name
) a,
( SELECT
a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM
sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE
a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY
a.tablespace_name
) b,
sys.DBA_TABLESPACES c
WHERE
a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
)
WHERE
tot_Pct_Used >=0
ORDER BY
tablespace_name;
Tablespace usage (2)
====================
select d.tablespace_name, d.file_name, d.bytes/1024/1024 Alloc_MB, f.bytes/1024/1024 Free_MB from dba_data_files d, dba_free_space f
where d.file_id=f.file_id order by 1;
select d.tablespace_name, sum(d.bytes/1024/1024) Alloc_MB, sum(f.bytes/1024/1024) Free_MB from dba_data_files d, dba_free_space f
where d.file_id=f.file_id group by d.tablespace_name order by 1;
Datafile added to Tablespace by date
====================================
select v.file#, to_char(v.CREATION_TIME, 'dd-mon-yy hh24:mi:ss') Creation_Date, d.file_name, d.bytes/1024/1024 MB
from dba_data_files d, v$datafile v where d.tablespace_name='XXGTM_DAT' and d.file_id = v.file#;
Added in last 72 hours
======================
select v.file#, to_char(v.CREATION_TIME, 'dd-mon-yy hh24:mi:ss') Creation_Date, d.file_name, d.bytes/1024/1024 MB
from dba_data_files d, v$datafile v where d.tablespace_name='XXGTM_DAT' and d.file_id = v.file# and v.creation_time > sysdate - 20;
Monitor SQL Execution History (Toad)
====================================
Set lines 200 pages 200
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs,
rows_processed_total Total_rows, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(DISK_READS_DELTA/decode(nvl(DISK_READS_DELTA,0),0,1,executions_delta)) avg_pio,SQL_PROFILE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where
sql_id = '9vv8244bcq529' and ss.snap_id = S.snap_id
and
ss.instance_number = S.instance_number
and executions_delta > 0 order by 1, 2, 3;
Check SQL Plan
==============
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('9vv8244bcq529'));
OHS Version
============
export ORACLE_HOME=/apps/envname/product/fmw
LD_LIBRARY_PATH=$ORACLE_HOME/ohs/lib:$ORACLE_HOME/oracle_common/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH
cd /apps/envname/product/fmw/ohs/bin
/apps/envname/product/fmw/ohs/bin > ./httpd -version
Find duplicate rows in a table.
===============================
set lines 1000
col ACTIVATION_ID for a50;
col USER_ID for a30;
SELECT ACTIVATION_ID, LFORM_ID,USER_ID FROM DBA_BTDEL1.LMS_LFORM_ACTIVATION GROUP BY ACTIVATION_ID, LFORM_ID,USER_ID HAVING count(*) > 1;
Partition Tables in database
============================
set lines 200 pages 200
col owner for a30
col table_name for a30
col partition_name for a30
select t.owner, t.table_name, s.PARTITION_NAME, s.bytes/1024/1024 MB from dba_tables t, dba_segments s
where t.partitioned = 'YES' and t.owner not in ('SYS','SYSTEM') and t.table_name=s.segment_name order by 2, 4;
Who is using my system tablespace
=================================
select owner, segment_type, sum(bytes/1024/1024) MB, count(*), tablespace_name from dba_segments
where tablespace_name in ('SYSTEM','SYSAUX')
group by owner, segment_type, tablespace_name order by 1;
What are the largest/biggest tables of my DB.
=============================================
col segment_name for a30
Select * from (select owner, segment_name, segment_type, bytes/1024/1024 MB from dba_segments order by bytes/1024/1024 desc) where rownum <=30;
ASM Disk Group Details
======================
cd /oracle/product/grid_home/bin
./kfod disks=all asm_diskstring='ORCL:*'
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 557693 Mb ORCL:DBPRD_AR_544G_01
2: 557693 Mb ORCL:DBPRD_DT01_544G_01
3: 557693 Mb ORCL:DBPRD_FRA_544G_01
4: 16378 Mb ORCL:DBPRD_RC_16G_001
5: 16378 Mb ORCL:DBPRD_RC_16G_002
6: 16378 Mb ORCL:DBPRD_RC_16G_003
7: 16378 Mb ORCL:DBPRD_RC_16G_004
8: 16378 Mb ORCL:DBPRD_RC_16G_005
9: 16378 Mb ORCL:DBPRD_RC_M_16G_001
10: 16378 Mb ORCL:DBPRD_RC_M_16G_002
11: 16378 Mb ORCL:DBPRD_RC_M_16G_003
12: 16378 Mb ORCL:DBPRD_RC_M_16G_004
13: 16378 Mb ORCL:DBPRD_RC_M_16G_005
14: 1019 Mb ORCL:GRID_NPRD_3026_CL_A_1G_1
15: 1019 Mb ORCL:GRID_NPRD_3026_CL_A_1G_2
16: 1019 Mb ORCL:GRID_NPRD_3026_CL_B_1G_1
17: 1019 Mb ORCL:GRID_NPRD_3026_CL_B_1G_2
18: 1019 Mb ORCL:GRID_NPRD_3026_CL_C_1G_1
19: 1019 Mb ORCL:GRID_NPRD_3026_CL_C_1G_2
./kfod disks=all asm_diskstring='/dev/oracleasm/disks/*'
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 557693 Mb /dev/oracleasm/disks/DBPRD_AR_544G_01 oracle dba
2: 557693 Mb /dev/oracleasm/disks/DBPRD_DT01_544G_01 oracle dba
3: 557693 Mb /dev/oracleasm/disks/DBPRD_FRA_544G_01 oracle dba
4: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_001 oracle dba
5: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_002 oracle dba
6: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_003 oracle dba
7: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_004 oracle dba
8: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_16G_005 oracle dba
9: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_001 oracle dba
10: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_002 oracle dba
11: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_003 oracle dba
12: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_004 oracle dba
13: 16378 Mb /dev/oracleasm/disks/DBPRD_RC_M_16G_005 oracle dba
14: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_A_1G_1 oracle dba
15: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_A_1G_2 oracle dba
16: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_B_1G_1 oracle dba
17: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_B_1G_2 oracle dba
18: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_C_1G_1 oracle dba
19: 1019 Mb /dev/oracleasm/disks/GRID_NPRD_3026_CL_C_1G_2 oracle dba
Clear SQL Cache
===============
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886
SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
no rows selected
Thread/dump
===========
jstack -l <pid> > <file-path>
kill -3 pid
Get the object name with block ID
==================================
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN segment_name FORMAT A24
COLUMN segment_type FORMAT A24
SELECT segment_name, segment_type, block_id, blocks
FROM dba_extents
WHERE
file_id = &file_no
AND
( &block_value BETWEEN block_id AND ( block_id + blocks ) )
/
DB link details
================
col DB_LINK for a30
col OWNER for a30
col USERNAME for a30
col HOST for a30
select * from dba_db_links;
1 note
·
View note