Common oracle operations

1. undo tablespace management: selects. username, u. namefromv $ transactiont, v $ rollstatr, v $ rollnameu, v $ sessionswheres. taddrt. addrandt. xidusnr. usnandroid. usnu. usnorderbys. username; query the current rollback segment usage: selects

1. undo tablespace management: select s. username, u. name from v $ transaction t, v $ rollstat r, v $ rollname u, v $ session s where s. taddr = t. addr and t. xidusn = r. usn and r. usn = u. usn order by s. username; query the current rollback segment usage: select s

1. undo tablespace Management

View the users using rollback segments:
Select s. username, u. name from v $ transaction t, v $ rollstat r, v $ rollname u, v $ session s where s. taddr = t. addr and t. xidusn = r. usn and r. usn = u. usn order by s. username;

Query the current rollback segment usage:
Select segment_name, tablespace_name, r. status, (initial_ext Ent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v. curext CurExtent From dba_rollback_segs r, v $ rollstat v Where r. segment_id = v. usn (+) and TABLESPACE_NAME = 'undotbs2 'order by segment_name;

Select segment_name, tablespace_name, r. status, (initial_ext Ent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v. curext CurExtent From dba_rollback_segs r, v $ rollstat v Where r. segment_id = v. usn (+) order by segment_name;

Create a new tablespace and expand it automatically:
Create undo tablespace undotbs2 datafile '/home/oracle/sagllf/undotbs1.dbf' size 5000 m reuse autoextend on next 800 m maxsize unlimited;

Set the undo tablespace:
Alter system set undo_tablespace = 'undotbs2 ';

Delete the original tablespace:
Drop tablespace UNDOTBS1 including contents and datafiles;

Set the undo_retention time
Alter system set undo_retention = 900 SCOPE = BOTH;

2. query table space usage

Select d. TABLESPACE_NAME,
SPACE "SUM_SPACE (M )",
MAXBYTES,
SPACE-NVL (FREE_SPACE, 0) "USED_SPACE (M )",
ROUND (SPACE-NVL (FREE_SPACE, 0 ))/
DECODE (MAXBYTES, 0, 1, MAXBYTES) * 100,
2) "USED_RATE (% )",
FREE_SPACE "FREE_SPACE (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) SPACE,
ROUND (SUM (DECODE (AUTOEXTENSIBLE, 'No', BYTES, MAXBYTES)/(1024*1024 ),
2) MAXBYTES
FROM DBA_DATA_FILES
Group by TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
Group by TABLESPACE_NAME) F
Where d. TABLESPACE_NAME = F. TABLESPACE_NAME (+)
Order by d. TABLESPACE_NAME;

3. query the lock table:
SELECT SESSION_ID from v $ LOCKED_OBJECT, USER_OBJECTS where v $ LOCKED_OBJECT.OBJECT_ID = USER_OBJECTS.OBJECT_ID;
Select serial # from v $ session where sid = SESSION_ID;
Alter system kill session '123 ';