Category: SQL scripts

wait.sql enqueue_type.sql lck.sql

select c.sid, c.serial#, c.username, a.object_id, b.object_name, c.program, c.status, d.name, c.osuser, e.sql_text, DBMS_ROWID.ROWID_CREATE(1, c.ROW_WAIT_OBJ#, c.ROW_WAIT_FILE#, c.ROW_WAIT_BLOCK#, c.ROW_WAIT_ROW# ) locked_rowid from v$Locked_object a, All_objects b, v$session c, audit_actions d, v$sqlarea e where a.object_id=b.object_id and a.session_id =c.sid(+) and c.command=d.action and c.row_wait_obj# != -1 and c.sql_id=e.sql_id;

Note:61552.1 Database Is Slow. (*) Any recent upgrade/degrade activities??? (2)Check Alert.log and trace files for any cause. (2) Check # of sessions, # of processes. (3) Check event init.ora parameter to see whether any event is set or not. (4) check # of log switching. (5) check archive log destinations space problem or ARCH may…

Read More “The most important Tuning Notes” »

*** This article is being delivered in Draft form and may contain errors. Please use the MetaLink “Feedback” button to advise Oracle of any issues related to this article. *** PURPOSE ——- This note includes new columns of V$SESSION and how to effectively use V$SESSION during contention in Oracle 10G. SCOPE & APPLICATION —————————– 1….

Read More “Oracle 10g Wait Model” »

Alter tablespace OLD_ts_name rename to NEW_TS_NAME;

select sql_text from V$sql a, v$sql_plan b where a.address = b.address and a.hash_value = b.hash_value and b.operation = ‘TABLE ACCESS’ and b.options = ‘FULL’ and b.OBJECT_OWNER ‘SYS’ /

set pagesize 2000 select text from user_source where name = upper(‘&procedure_name’) order by line;

REM =============================================================================== REM = Script Name : db_status.sql = REM = Usage : This script is to be used to give an overview of the current status = REM = of a given database. Things like suspect default and temporary = REM = tablespaces, tablespaces that have less than 20% free, objects that = REM…

Read More “db_status.sql” »

set pagesize 300 set linesize 205 set head on set echo off spool tblwopk accept usrid prompt ‘Enter User Name : ‘ REM Find all tables that does not have primary key on them. select /*+ rule */ a.table_name from dba_tables a where a.owner = upper(‘&usrid’) and a.table_name not in ( select b.table_name from dba_constraints…

Read More “tblwopk.sql /* Find Tables Without PK */” »

set pagesize 300 set linesize 205 set head on set echo off spool tblwopk accept usrid prompt ‘Enter User Name : ‘ accept table_name prompt ‘Enter Table Name : ‘ column PrimaryKey format a80 heading ‘Follwoing Are Primary Keys’ select substr(a.column_name, 1, 20) || ‘ Position : ‘ || to_char(a.position) PrimaryKey from dba_cons_columns a, dba_constraints…

Read More “find_pk.sql /* Find Primary Key */” »