1. Command to list down the database data files for a specific tablespace name :
select FILE_NAME, BYTES/1024/1024, MAXBYTES/1024/1024, MAXBLOCKS/1024/1024 from dba_data_files where TABLESPACE_NAME='<Tablespace name >';
2. Command to alter the data file:
alter database datafile '/u05/oradata/HYPDEV/<database name>XX.dbf' autoextend on maxsize 6144M;
select FILE_NAME, BYTES/1024/1024, MAXBYTES/1024/1024, MAXBLOCKS/1024/1024 from dba_data_files where TABLESPACE_NAME='<Tablespace name >';
2. Command to alter the data file:
alter database datafile '/u05/oradata/HYPDEV/<database name>XX.dbf' autoextend on maxsize 6144M;
3. Command to alter the data file of a table space:
alter tablespace HYPDEV_STAGE add datafile '/u05/oradata/HYPDEV/hypdev_stage02.dbf' size 100M autoextend on maxsize 2048M;
4. listing all the schema users whether there is a data or not in the DB:
select username from all_users order by username;
5. Grant select to a table or view available on a database
eg: grant select on SAPSR3."/BIC/AZISU_D0200" to <user>;
6. Drop the data file:
alter tablespace users drop datafile '/u04/oradata/AGSBX/SYSAUX02.dbf';
OR
alter database datafile <file_name> offline drop;
7. Drop the temp tablespace datafile:
alter database tempfile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\<filename>_.TMP' drop including datafiles;
8. Bring the data file online:
alter database datafile '/u04/oradata/AGSBX/SYSAUX02.dbf' online;
9. List down the active and used datafiles ,available space, max assigned data file size and autoextenstion on / off :
List down the data files and their left over space within the database:
set pages 10000
col tablespace_name format a20
col file_name format a45
col file_mb format 999999
col data_mb format 999999
col free_mb format 999999
col max_mb format 999999
select files.tablespace_name, files.file_name, ceil(files.mb) file_mb, ceil(files.mb - nvl(free.freemb,0)) data_mb,
nvl(free.freemb,0) free_mb, files.maxmb max_mb, autoextensible
from
(select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,
decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb
from dba_data_files) files,
(select file_id, sum(bytes)/1024/1024 freemb
from dba_free_space
group by file_id) free
where files.file_id = free.file_id (+)
and files.autoextensible='YES'
order by 1,2;
There is a more detailed (The actual max size allocated to tablespace, max assigned at this time by the database to the datafile) for tablespaces can be found with the below command -
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
10. PGA aggregates and usage details for a database
SQL> select name, value/1024/1024 "MB" from v$pgastat where name like '%PGA%';
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 200
aggregate PGA auto target 36.0527344
total PGA inuse 159.981445
total PGA allocated 185.317383
maximum PGA allocated 288.469727
total freeable PGA memory 13.8125
PGA memory freed back to OS 48684.625
total PGA used for auto workareas 0
maximum PGA used for auto workareas 27.3837891
total PGA used for manual workareas 0
maximum PGA used for manual workareas .263671875
11 rows selected.
11. List of the database objects in Oracle :
select count(*), object_type from dba_objects group by object_type;
select count(*) from dba_objects group by object_type;
select count(*) from dba_objects;
select count(*), owner, object_type from dba_objects where owner='DSXN_INFOSWD' group by owner,object_type;
select count(*), owner from dba_objects group by owner;
11. List of the active sessions to the database.
select SERIAL#, SCHEMA#, STATUS, USERNAME, SERVER, machine from v$session where USERNAME='PROCEDURES';
to kill the session
select sid, prev_exec_start, seconds_in_wait from v$session where username = 'TRAINTRACK_SFDC' and osuser='webadmin';
12. alter system switch logfile; --> this is asynchronous
where as " alter system archive log current" is synchronous ; meaning it will for the archiver process (ARCH) to complete writing the online redo log to the archivelog log filesystem , mostly suitable for RAC where it switches the log file for all the nodes in RAC.
13. Creating the synonym and give select previliges to the user BIREMOTE.
login with owner:
grant select on SAPPRD.ZQM_RECOMP_Z1_A to BIREPORT;
create synonym BIREPORT.ZQM_RECOMP_Z1_A for SAPPRD.ZQM_RECOMP_Z1_A;
To create the synonyms for all the tables within a schema:
(for eg: to be able read all the tables under SAPCL3 for the user
CLM_BO_READ)
select 'create synonym CLM_BO_READ.' || table_name || ' for SAPCL3.' || table_name || ';'from user_tables;
Once you have the list; save it in a .sql file login to sqlplus with sysdba and run as below.
sqlplus >spool <filename>.txt --> log file
sqlplus >@<script file>.sql
14. List of the user/schema name and their default tablespace names:
select username, default_tablespace, temporary_tablespace from dba_users;
15. List of the first large 15 database objects (tables / indexes)
SELECT * FROM
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 15
16. List of user sessions connected to the DB
alter tablespace HYPDEV_STAGE add datafile '/u05/oradata/HYPDEV/hypdev_stage02.dbf' size 100M autoextend on maxsize 2048M;
4. listing all the schema users whether there is a data or not in the DB:
select username from all_users order by username;
5. Grant select to a table or view available on a database
eg: grant select on SAPSR3."/BIC/AZISU_D0200" to <user>;
6. Drop the data file:
alter tablespace users drop datafile '/u04/oradata/AGSBX/SYSAUX02.dbf';
OR
alter database datafile <file_name> offline drop;
7. Drop the temp tablespace datafile:
alter database tempfile 'C:\ORACLE\ORADATA\ORCL\DATAFILE\<filename>_.TMP' drop including datafiles;
8. Bring the data file online:
alter database datafile '/u04/oradata/AGSBX/SYSAUX02.dbf' online;
9. List down the active and used datafiles ,available space, max assigned data file size and autoextenstion on / off :
List down the data files and their left over space within the database:
set pages 10000
col tablespace_name format a20
col file_name format a45
col file_mb format 999999
col data_mb format 999999
col free_mb format 999999
col max_mb format 999999
select files.tablespace_name, files.file_name, ceil(files.mb) file_mb, ceil(files.mb - nvl(free.freemb,0)) data_mb,
nvl(free.freemb,0) free_mb, files.maxmb max_mb, autoextensible
from
(select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,
decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb
from dba_data_files) files,
(select file_id, sum(bytes)/1024/1024 freemb
from dba_free_space
group by file_id) free
where files.file_id = free.file_id (+)
and files.autoextensible='YES'
order by 1,2;
There is a more detailed (The actual max size allocated to tablespace, max assigned at this time by the database to the datafile) for tablespaces can be found with the below command -
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
10. PGA aggregates and usage details for a database
SQL> select name, value/1024/1024 "MB" from v$pgastat where name like '%PGA%';
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 200
aggregate PGA auto target 36.0527344
total PGA inuse 159.981445
total PGA allocated 185.317383
maximum PGA allocated 288.469727
total freeable PGA memory 13.8125
PGA memory freed back to OS 48684.625
total PGA used for auto workareas 0
maximum PGA used for auto workareas 27.3837891
total PGA used for manual workareas 0
maximum PGA used for manual workareas .263671875
11 rows selected.
11. List of the database objects in Oracle :
select count(*), object_type from dba_objects group by object_type;
select count(*) from dba_objects group by object_type;
select count(*) from dba_objects;
select count(*), owner, object_type from dba_objects where owner='DSXN_INFOSWD' group by owner,object_type;
select count(*), owner from dba_objects group by owner;
11. List of the active sessions to the database.
select SERIAL#, SCHEMA#, STATUS, USERNAME, SERVER, machine from v$session where USERNAME='PROCEDURES';
to kill the session
ALTER SYSTEM KILL SESSION '<SID>, <SERIAL>'select sid, prev_exec_start, seconds_in_wait from v$session where username = 'TRAINTRACK_SFDC' and osuser='webadmin';
12. alter system switch logfile; --> this is asynchronous
where as " alter system archive log current" is synchronous ; meaning it will for the archiver process (ARCH) to complete writing the online redo log to the archivelog log filesystem , mostly suitable for RAC where it switches the log file for all the nodes in RAC.
13. Creating the synonym and give select previliges to the user BIREMOTE.
login with owner:
grant select on SAPPRD.ZQM_RECOMP_Z1_A to BIREPORT;
create synonym BIREPORT.ZQM_RECOMP_Z1_A for SAPPRD.ZQM_RECOMP_Z1_A;
To create the synonyms for all the tables within a schema:
(for eg: to be able read all the tables under SAPCL3 for the user
CLM_BO_READ)
select 'grant select on '||owner||'.'||table_name|| ' to CLM_BO_READ;' from dba_tables where owner='SAPCL1'
select 'create synonym CLM_BO_READ.' || table_name || ' for SAPCL3.' || table_name || ';'from user_tables;
Once you have the list; save it in a .sql file login to sqlplus with sysdba and run as below.
sqlplus >spool <filename>.txt --> log file
sqlplus >@<script file>.sql
14. List of the user/schema name and their default tablespace names:
select username, default_tablespace, temporary_tablespace from dba_users;
15. List of the first large 15 database objects (tables / indexes)
SELECT * FROM
(
select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc
) WHERE
ROWNUM <= 15
16. List of user sessions connected to the DB
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';
+++++++++++++++++++++++++++++++
SELECT s.sid, s.serial#,
s.username,s.status, s.osuser,s.process, s.program, s.type, s.event, s.p1text, s.service_name,p.spid,p.pname,
s.machine, p.terminal, s.program,to_char(s.logon_time,'DD-MON-YY HH24:MI:SS'),
p.tracefile 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;
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# FORMAT 999,999,999
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# = n.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 28824. 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'));
No comments:
Post a Comment