17. The syntax to shrink space and rebuild index:
For table: CMS_INFOOBJECTS7:
alter table <TABLE_NAME> enable row movement;
alter <TABLE_NAME> shrink space;
For Index OBJNAME_I7 :
ALTER INDEX <INDEX_NAME> REBUILD ONLINE.
18. Database size :
An oracle database consists of data files, redo log files, control files, temporary files.
The size of the database actually means the total size of all these files.
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;
19. Where can we use Incremental backup Vs full backup ?
Restore always looks for level 0 backup ( full backup), not the incremental backups.
Incremental backups comes in picture during recovery.
During Recovery, Oracle looks for incremental backups, if exists, it
will do the recovery with the incremental backups, if no incremental
backups available, then, it will look for archived logs.
Therefore, incremental backups never used during restore, they are used only during the recovery.
20. SQL Queries executing on a Oracle database -
select v$session.sid,v$session.serial#,v$session.username,v$session.sql_id,v$session.sql_child_number,optimizer_mode,hash_value,address,sql_text
from v$sqlarea ,v$session
where v$session.sql_hash_value = v$sqlarea.hash_value
and v$session.sql_address = v$sqlarea.address
and v$session.username is not null;
21. List all the grants assigned to the user in oracle database -
System privileges for a user:
SELECT PRIVILEGE
FROM sys.dba_sys_privs
WHERE grantee = 'BIREPORT'
UNION
SELECT PRIVILEGE
FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
WHERE rp.grantee = 'BIREPORT'
ORDER BY 1;
Direct grants to tables/views:
SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv
FROM table_privileges
WHERE grantee = 'BIREPORT'
ORDER BY owner, table_name;
Indirect grants to tables/views:
SELECT DISTINCT owner, table_name, PRIVILEGE
FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
WHERE rp.grantee = 'BIREPORT'
ORDER BY owner, table_name;
22. High CPU usage SQL sessions based on the high CPU used by the top command -
connect to the DB that has the high oracle process CPU used -
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000
SELECT
s.username,
t.sid,
s.serial
SUM(VALUE/100) as "cpu usage (seconds)"
FROM
v$session s,
v$sesstat t,
v$statname n
WHERE
t.STATISTIC
AND
NAME like '%CPU used by this session%'
AND
t.SID = s.SID
AND
s.status='ACTIVE'
AND
s.username is not null
GROUP BY username,t.sid,s.serial
/
23. Query to check the number of sessions configured vs used & max used for a database.
select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');
Output as below -
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------------------------ ------------------- --------------- ----------------------------------------
processes 79 142 150
sessions 87 150 288
24. table size calculation -
First, gather optimiser stats on the table (if you haven't already):
begin
dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/
select blocks, empty_blocks, num_freelist_blocks from all_tables where owner = 'AGILEREG' and table_name = 'REGPROJECTDATA';
25. TABLESPACE QUOTA DETAILS OF ALL THE USERS:
set pagesize 200
set lines 200
col ownr format a20 justify c heading 'Owner'
col name format a20 justify c heading 'Tablespace' trunc
col qota format a12 justify c heading 'Quota (KB)'
col used format 999,999,990 justify c heading 'Used (KB)'
set colsep '|'
select
username ownr,
tablespace_name name,
decode(greatest(max_bytes, -1),
-1, 'UNLIMITED',
to_char(max_bytes/1024, '999,999,990')
) qota,
bytes/1024 used
from
dba_ts_quotas
where
max_bytes!=0
or
bytes!=0
order by
1,2
/
26. Get database growth by month and year.
col Month format a30
select to_char(creation_time, 'RRRR Month') "Month",sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile
where creation_time > SYSDATE-365
group by creation_time
col year format a20
select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from v$datafile
group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM')
order by 1, 2;
27. checking the parameter dynamic or need db restart
select name, issys_modifiable from V$PARAMETER2 where name='optimizer_dynamic_sampling';
28.
Lock related queries :
SELECT XIDUSN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS from v$locked_object;
SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE
from v$locked_object l, dba_objects d
where d.OBJECT_ID=l.OBJECT_ID;
SELECT ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, BLOCK from v$lock;
SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
SELECT s.sid, l.lmode, l.block, substr(s.username, 1, 10), substr(s.schemaname, 1, 10),
substr(s.osuser, 1, 10), substr(s.program, 1, 30), s.command
FROM v$session s, v$lock l
WHERE s.sid=l.sid;
SELECT p.spid, s.sid, p.addr,s.paddr,substr(s.username, 1, 10), substr(s.schemaname, 1, 10),
s.command,substr(s.osuser, 1, 10), substr(s.machine, 1, 10)
FROM v$session s, v$process p
WHERE s.paddr=p.addr
SELECT sid, serial#, command,substr(username, 1, 10), osuser, sql_address,LOCKWAIT,
to_char(logon_time, 'DD-MM-YYYY;HH24:MI'), substr(program, 1, 30)
FROM v$session;
SELECT sid, serial#, username, LOCKWAIT from v$session;
SELECT v.SID, v.BLOCK_GETS, v.BLOCK_CHANGES, w.USERNAME, w.OSUSER, w.TERMINAL
FROM v$sess_io v, V$session w
WHERE v.SID=w.SID ORDER BY v.SID;
SELECT * from dba_waiters;
SELECT waiting_session, holding_session, lock_type, mode_held
FROM dba_waiters;
SELECT
p.spid unix_spid,
s.sid sid,
p.addr,
s.paddr,
substr(s.username, 1, 10) username,
substr(s.schemaname, 1, 10) schemaname,
s.command command,
substr(s.osuser, 1, 10) osuser,
substr(s.machine, 1, 25) machine
FROM v$session s, v$process p
WHERE s.paddr=p.addr
ORDER BY p.spid;
29. The query to check the RMAN catalog to list the backups
connect to the DB using catalog dbuser and password. And then , you can copy / paste the below commands altogether.
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));
.png)
.JPG)