Category Script

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, […]

Current Break Down of (SGA) Variable Size


prompt . prompt . prompt Current Break Down of (SGA) Variable Size prompt ————— column Bytes format 999,999,999 column “% Used” format 999.99 column “Var. Size” format 999,999,999 select a.name “Name”, bytes “Bytes”, (bytes / b.value) * 100 “% Used”, b.value “Var. Size” from sys.v_$sgastat a, sys.v_$sga b where a.name not in (‘db_block_buffers’,’fixed_sga’,’log_buffer’) and b.name=’Variable […]