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…
*** 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….
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…
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…
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…
set pagesize 300 set linesize 205 set head on set echo off spool fkwoind accept usrid prompt ‘Enter User Name : ‘ col “Cons Name” format a30 heading “Cons Name” word_wrapped col status format a10 heading status col “Cons Type” format a12 heading “Cons Type” col “Column Name” format a30 heading “Column Name” col “Cons…
