Note of DBA task

Find out user quota in a table space

SELECT * FROM dba_ts_quotas
WHERE USERNAME='nama_user'
AND TABLESPACE_NAME='nama_tablespace';

Find out table space

SELECT b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
FROM  (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024 ,2) AS free_space
       FROM dba_free_space
       GROUP BY tablespace_name) a,
      (SELECT tablespace_name, SUM(bytes)/1024/1024 AS tbs_size
       FROM dba_data_files
       GROUP BY tablespace_name) b
WHERE a.tablespace_name(+)=b.tablespace_name;

Identify User  Session

Identify Session User
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
ORDER BY s.username;
 

Count Session and group by machine

select count(*), machine from v$session group by machine order by 1

Moving table space of a table.

–alter table USAGE_HISTORY move tablespace MM_USAGE
–alter index <iname> rebuild tablespace <tablespace name>
–alter index <index name> tablespace MM_USAGE_INDX

–alter index SYS_C0015263 rebuild tablespace MM_USAGE_INDX

Advertisement