Category Script

How to check high resource intensive SQL in Oracle


Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then […]

enable_novalidate_constraint


select ‘ alter table ‘||table_name|| ‘ enable novalidate constraint ‘ ||constraint_name ||’;’ from user_constraints where table_name =’TABLE_NAME’

create a new user (with privs) like an existing


set pages 0 feed off veri off lines 500 accept oldname prompt “Enter user to model new user to: ” accept newname prompt “Enter new user name: ” — accept psw prompt “Enter new user’s password: ” — Create user… select ‘create user &&newname identified by values ”’||password||””|| — select ‘create user &&newname identified by […]

Blocking Sessions in Oracle RAC


select blocking_session,blocked_session,script from ( select distinct s1.username || ‘@’ || s1.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s1.sid || ‘ ET=’ || s1.last_call_et || ‘sn. STATUS=’ || s1.status || ‘ EVENT=’ || s1.event || ‘ ACTION= ‘ || s1.action || ‘ PROGRAM=’ || s1.program || ‘ MODULE=’ || s1.module || ‘)’ […]

Automatic Statistics Collection script


prompt set feed off prompt Auto tasks (11G) prompt accept x_days prompt “Days [1] : ” default 1 prompt select decode( b.instance_number, a.inst_id, ‘–>’, null ) “Current”, a.instance_name, a.host_name, a.version, a.startup_time, a.status, a.parallel, a.thread#, a.archiver, a.log_switch_wait, a.logins, a.shutdown_pending, a.database_status, a.instance_role, a.active_state, a.blocked, a.instance_number from gv$instance a, v$instance b where b.instance_number (+)= a.inst_id order by a.inst_id; […]

SHUTDOWN THE DATABASE WITH IMMEDIATE MODE / Shell script


echo “—————————————————————————-” echo Report by Anar Godjaev Oracle 9i/10g/11g OCA,OCP,SQL Expert, PL/SQL Prof,RAC echo “—————————————————————————-” #!/bin/ksh # This shell script performs the following steps # shutdown the database with immediate mode # log file ORACLE_HOME=/data1/oracle/product/8.1.7 ; export ORACLE_HOME ORACLE_SID=ORCL export ORACLE_SID LOGFILE=/data1/oracle/admin/ORCL/log/anar.log clear echo “Obase-1 the database is closed.” echo ” Are you sure you? […]

Apache Tomcat –shutdown/startup script (shell)


startup.sh/Shutdown.sh   /  Script is useful for you !!!!!!!!! #!/bin/sh # —————————————————————————– # Stop script for the CATALINA Server # # $Id: shutdown.sh,v 1.3 2002/08/04 18:19:43 patrickl Exp $ # —————————————————————————– # resolve links – $0 may be a softlink PRG=”$0″ while [ -h “$PRG” ] ; do   ls=`ls -ld “$PRG”`   link=`expr “$ls” : […]

View all currently locked objects:


SELECT username U_NAME, owner OBJ_OWNER, object_name, object_type, s.osuser, DECODE(l.block, 0, ‘Not Blocking’, 1, ‘Blocking’, 2, ‘Global’) STATUS, DECODE(v.locked_mode, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(lmode) ) MODE_HELD FROM gv$locked_object v, dba_objects d, gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) […]

how to find different objects to different other database used by database link


select index_name as “ANARDB indexes” from dba_indexes where owner like ‘OC_%’ minus select index_name from dba_indexes@ANARDB1 where owner like ‘OC_%’; select sequence_name as “ANARDB sequences” from dba_sequences where sequence_owner like ‘OC_%’ minus select sequence_name from dba_sequences@ANARDB1 where sequence_owner like ‘OC_%’ select table_name “ANARDB tables” from dba_tables where owner like ‘OC_%’ minus select table_name from dba_tables@ANARDB1 […]

Create Public Synonym


SELECT ‘Create or replace public synonym  ‘||object_name || ‘ for ‘                                || OWNER || ‘.’ || object_name || ‘;’ FROM ALL_OBJECTS Where object_type  in (‘VIEW’,’PACKAGE’) and OWNER = ‘<USER_NAME>’;

How to find (Long) Running (active/hung) Transactions in Oracle database?


select t.start_time, s.sid,s.serial#,s.username,s.status,s.schemaname, s.osuser,s.process,s.machine,s.terminal,s.program,s.module,s.type, to_char(s.logon_time,’DD/MON/YY HH24:MI:SS’) logon_time from v$transaction t, v$session s where s.saddr = t.ses_addr and s. status = ‘ACTIVE’ order by start_time

find tables without PK constraint


SELECT table_name FROM all_tables WHERE owner = ‘&OWNER’ MINUS SELECT table_name FROM all_constraints WHERE owner = ‘&&OWNER’ AND constraint_type = ‘P’; Script: To find out which primary keys are disabled SELECT owner, constraint_name, table_name, status FROM all_constraints WHERE owner = ‘&OWNER’ AND status = ‘DISABLED’ AND constraint_type = ‘P’;

exadata session kill


SELECT ‘ALTER SYSTEM KILL SESSION (‘ || ”” || sid || ‘,’ || serial# || ‘,’ || ‘@’ || inst_id || ”’) immediate; ‘ FROM gv$session WHERE TYPE <> ‘BACKGROUND’;

dbms_space


set serveroutput on declare total_blocks NUMBER ; total_bytes NUMBER ; unused_blocks NUMBER; unused_bytes NUMBER ; last_used_extent_file_id NUMBER ; last_used_extent_block_id NUMBER ; last_used_block NUMBER ; BEGIN dbms_space.unused_space(‘&Username’,’&Table_Name’ ,’TABLE’ , total_blocks,total_bytes, unused_blocks ,unused_bytes, last_used_extent_file_id , last_used_extent_block_id , last_used_block, ‘&Partition_Name’ ); dbms_output.put_line( ‘********************************************************************************’); dbms_output.put_line( ‘total number of blocks in the segment :’||TO_CHAR(total_blocks) ); dbms_output.put_line( ‘Total Mbytes :’||TO_CHAR(total_bytes/1024/1024) ); […]

Temp Tablespace WARNING- These used% > 85% (add temp file)


select tsh.tablespace_name, sum(tsh.bytes_used+tsh.bytes_free) allocated_space,round(sum( tsh.bytes_used)*100/( sum(tsh.bytes_used+tsh.bytes_free)),2) “USED %” from V$TEMP_SPACE_HEADER tsh group by tsh.tablespace_name;

LIST OF OBJECTS HAVING > 12 EXTENTS (DBA_EXTENTS)


column Tablespace_ext format a30 column Segment format a40 column Count format 9999 break on “Tablespace_ext” skip 1 select tablespace_name “Tablespace_ext” , owner “Owner”, segment_name “Segment”, count(*) “Count” from sys.dba_extents group by tablespace_name,owner,segment_name having count(*)>12 order by 1,3 desc;

ERROR- These segments will fail during NEXT EXTENT (DBA_SEGMENTS)


column Tablespaces format a30 column Segment format a40 column “NEXT Needed” format 999,999,999 column “MAX Available” format 999,999,999 select a.tablespace_name “Tablespaces”, a.owner “Owner”, a.segment_name “Segment”, a.next_extent “NEXT Needed”, b.next_ext “MAX Available” from sys.dba_segments a, (select tablespace_name,max(bytes) next_ext from sys.dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name(+) and b.next_ext < a.next_extent;

WARNING- These segments > 70% of MAX EXTENT (DBA_SEGMENTS)


column Tablespace format a30 column Segment format a40 column Used format 9999 column Max format 9999 select tablespace_name “Tablespace”, owner “Owner”, segment_name “Segment”, extents “Used”, max_extents “Max” from sys.dba_segments where (extents/decode(max_extents,0,1,max_extents))*100 > 70 and max_extents >0;

Full compile invalid obje by PL/SQL code


grant execute on SYS.FULL_COMPILE to ANAR; CREATE OR REPLACE PROCEDURE SYS.FULL_COMPILE(xfull IN BOOLEAN,xowner IN VARCHAR2,xoutput IN BOOLEAN) AS CURSOR c1 IS SELECT ‘ALTER ‘ || object_type || ‘ ‘ || owner || ‘.’ || object_name || ‘ COMPILE’ strsql FROM dba_objects WHERE status = ‘INVALID’ AND object_type IN (‘PROCEDURE’,’FUNCTION’,’VIEW’,’PACKAGE’,’TRIGGER’) AND owner like xowner UNION ALL SELECT ‘ALTER PACKAGE’ […]

TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE)


prompt . prompt ====================================== prompt . TABLESPACE USAGE (DBA_DATA_FILES, DBA_FREE_SPACE) prompt ====================================== column Tablespace format a30 column Size format 999,999,999,999 column Used format 999,999,999,999 column Free format 999,999,999,999 column “% Used” format 999.99 select tablespace_name “Tablesapce”, bytes “Size”, nvl(bytes-free,bytes) “Used”, nvl(free,0) “Free”, nvl(100*(bytes-free)/bytes,100) “% Used” from( select ddf.tablespace_name, sum(dfs.bytes) free, ddf.bytes bytes FROM (select tablespace_name, […]