Database & Software

HOW TO CHECK THE LOCKED OBJECTS IN THE DATABASE


SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,1,’NULL’,2,’ROW SHARE’,3,’ROW EXCLUSIVE’,4,’SHARE’,5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and    l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and    s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by  l.inst_id ;

select * from sys.GV_$LOCKED_OBJECT;


HOW TO CHECK THE TABLESPACE SIZE


SELECT
tablespace_name,
SUM(bytes/1024/1024)
FROM
dba_segments
GROUP BY
tablespace_name;


CHECK WHICH TABLE IS BELONG TO WHICH TABLESPACE


select t.table_name,t.tablespace_name,df.file_name from dba_tables t, dba_data_files df
where t.tablespace_name = df.tablespace_name and t.tablespace_name not in( ‘SYSAUX’,’SYSTEM’)
order by t.table_name;


HOW TO SEE THE TABLESPACE INFORMATIONS


select file_name, bytes, autoextensible, maxbytes
from dba_data_files;


HOW TO CHECK THE TABLESPACE STATUS & SIZE


select tablespace_name,status from dba_tablespaces;
select file_name,bytes,maxbytes,user_bytes from dba_data_files where tablespace_name
=’USERS’;
select * from dba_data_files;


HOW TO CHECK THE USED & FREE SPACE IN TABLESPACE


SELECT /* + RULE */ df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024)”Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

SELECT /* + RULE */ df.file_name ,df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024)”Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT file_name,tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY file_name,tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.file_name
UNION ALL
SELECT /* + RULE */ fs.file_name,df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used,fs.file_name
ORDER BY 1 DESC;


HOW TO INCREASE THE TABLESPACE SIZE


ALTER TABLESPACE USERS
ADD DATAFILE ‘/data/oradata/ECSSHDBD/users02.dbf’
SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 1900M;
ALTER TABLESPACE <tablespace name> ADD DATAFILE ‘<location>’ SIZE nG AUTOEXTEND ON MAXSIZE
XXG;


HOW TO VIEW ALL CURRENTLY LOCKED OBJECTS IN DATABASE


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)
AND  v.session_id = s.sid
ORDER BY username, session_id;


HOW TO SEE LIST OF OBJECTS THAT HAVE BEEN LOCKED FOR 60 SECONDS OR MORE


SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) “WAITING User”,
SUBSTR(s1.osuser,1,8) “OS User”,
SUBSTR(s1.program,1,20) “WAITING Program”,
s1.client_info “WAITING Client”,
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) “HOLDING User”,
SUBSTR(s2.osuser,1,8) “OS User”,
SUBSTR(s2.program,1,20) “HOLDING Program”,
s2.client_info “HOLDING Client”,
o.object_name “HOLDING Object”
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;


How To Disable All Constraint


BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = ‘ENABLED’
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement(‘alter table “‘ || c.owner || ‘”.”‘ || c.table_name || ‘” disable constraint ‘ || c.constraint_name);
END LOOP;
END;
/

DECLARE

cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;

BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
if c1.table_name = c2.table_name and c1.status = ‘ENABLED’ THEN
dbms_utility.exec_ddl_statement(‘alter table ‘ || c1.owner || ‘.’ || c1.table_name || ‘ disable constraint ‘ || c1.constraint_name);
end if;
end loop;
END LOOP;
END;
/


How To Enable All Constraint


BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = ‘DISABLED’
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement(‘alter table “‘ || c.owner || ‘”.”‘ || c.table_name || ‘” enable constraint ‘ || c.constraint_name);
END LOOP;
END;
/
DECLARE

cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;

BEGIN
FOR c1 IN r1
loop
for c2 in r2
loop
if c1.table_name = c2.table_name and c1.status = ‘DISABLED’ THEN
dbms_utility.exec_ddl_statement(‘alter table ‘ || c1.owner || ‘.’ || c1.table_name || ‘ disable constraint ‘ || c1.constraint_name);
end if;
end loop;
END LOOP;
END;
/