100642413-DBA-Scripts

•Home
•TOOLS
•PL SQL
•SCRIPTS
•OAF
•AP
•PO
•AR
•OM
•PA
•INV
•HRMS
•GL
•ALL
Showing posts with label DBA. Show all posts
Saturday, January 28, 2012
Data Base Queries
Full database Size
select round(sum(Gig),2) Gig from
(
select sum(bytes)/(1024*1024*1024) Gig from dba_data_files
union
select sum(bytes)/(1024*1024*1024) Gig from dba_temp_files
union
select sum(v$log.bytes)/(1024*1024*1024) Gig from v$log, v$logfile
where v$log.group# = v$logfile.group#
)
Tablespace TEMP

SELECT tablespace_name, SUM(bytes_used)/1024/1024 MB_USED,
SUM(bytes_free)/1024/1024 MB_FREE
FROM V$temp_space_header GROUP BY tablespace_name;
HOW TO CHECK TABLESPACE SIZE
CHECK SIZE OF DATABASE
SQL) select round(sum(Gig),2) Gig from
(
select sum(bytes)/(1024*1024*1024) Gig from dba_data_files
union
select sum(bytes)/(1024*1024*1024) Gig from dba_temp_files
union
select sum(v$log.bytes)/(1024*1024*1024) Gig from v$log, v$logfile
where v$log.group# = v$logfile.group#
)
Query to see the Reserved, Used and Free space of your database
SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;
SQL> select username,status,type from v$session;
SQL> desc DBA_ROLE_PRIVS
SQL> select name,open_mode from v$database
SQL> set lines 120
SQL> col FILE_NAME for a55;
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space
group by tablespace_name;
'user_data' order by 2 desc;
alter tablespace BUMT_DATA01 add datafile’/oradata/fs03/bumt1p/bumt_data01_41.dbf’ size 10240m
SQL> /
select a.TABLESPACE_NAME, a.BYTES MB_TOTAL, b.BYTES MB_free, b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)

percent_used from (select TABLESPACE_NAME, sum(BYTES/1024/1024) BYTES from
dba_data_files group by TABLESPACE_NAME)a,
(select TABLESPACE_NAME, sum(BYTES/1024/1024) BYTES , max(BYTES/1024/1024) largest
from dba_free_space group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES)
desc;
SQL> fdf | grep oradata
select ( a.data_size+b.temp_size+c.redo_size)/1024/1024/1024 "DB Size(GB)" from ( select sum(bytes)
data_size from dba_data_files ) a, ( select nvl (sum(bytes),0) temp_size from dba_temp_files ) b, ( select
sum(bytes) redo_size from sys.v_$log ) c;
TEMPEPORARY TABLESPACE:
SELECT tablespace_name,
SUM(bytes_used)/1024/1024 "USED MB",
SUM(bytes_free)/1024/1024 "FREE MB"
FROM V$temp_space_header
GROUP BY tablespace_name;
SQL> select file_name,bytes/1024/1024 from dba_temp_files where TABLESPACE_NAME='TEMP';
SELECT tablespace_name,SUM(bytes_used)/1024/1024 "USED MB", SUM(bytes_free)/1024/1024
"FREE MB" FROM V$temp_space_header GROUP BY tablespace_name;
select file_name,bytes/1024/1024 from dba_temp_files where TABLESPACE_NAME='TEMP'
order by 2;
alter tablespace TEMP1 add tempfile ' /oradata/tcrmapp/fs01//temp_01.dbf' size 1024m
alter database tempfile ' /oradata/fs05/dat2/temp_04.dbf' resize 2048M;
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) allocated,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') free,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') USed
from dual
/
DATABASE (ORACLE COMMANDS)
SQL> select name,open_mode from v$database;
SQL> show parameter pfile;

SQL> show parameter sessions;
SQL> show parameter precesses;
SQL> select count(*) from v$session;
BLOKCING SESSIONS
* To find blocking session jobs below query will useful. It will return two rows.
select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365
* Second step to find the serial number for the Blocking Session to kill
select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130
SQL> select SADDR,SID,SERIAL# from v$session where process=’365’;
•Final step to kill the blocking session
• alter system kill session ‘SID,SERIAL#’;
SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);
SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE
E.g.
SQL> alter system kill session ‘365,130′;
System altered.
select sid, status from V$session where sid IN ('421,27569')
DATABASE USER
SQL> alter user nilesh account lock;

SQL> alter user nilesh account unlock;
HOW TO CHECK MEMORY
SQL>select name, value from v$parameter
where name in ( 'sga_max_size', 'shared_pool_size','db_cache_size' , 'large_pool_size');
MOVE ORAARCH files
Run this command form the which mount point is full Exp : /oraarch/fs01/
+600------time before 6hrs
We can remove in the archive directory with the extension “.BU” those are already have taken backup
Unix command------
Find and Move
find ./ -cmin +100 -exec mv '{}' /orabkup/fs02/lsomsi01/bkup_arc_26_02_10 \;
To Remove the archive --à +7 is 7days
find /oraarch/pkg1/fs01/TCP2/BU.redo*.Z -mtime +7 -exec rm {} \;
find /oraarch/pkg1/fs01/LDCCCP2/BU.redo*.Z -mtime +7 -exec rm {} \;
USER
Select status from dba_users where username=
To take the archive backup
nohup /usr/local/opt/oracle/local/bin/runbkuplog -s lsomsi01 &
/orabkup/fs02/lsomsi01/bkup_arc_26_02_10
To Check the tablespace free
column tablespace_name heading 'Tablespace' format a25
column status heading 'Status' format a8
column total_mb heading 'Size MB' format 999,999.9
column used_mb heading 'Used MB' format 999,999.9
column avail_mb heading 'Avail MB' format 999,999.9
column pct_used heading '% Used' format 99999.9
select
ts.tablespace_name,
ts.status,
ts.total_kb/1024 total_mb,
seg.used_kb/1024 used_mb,
(ts.total_kb - seg.used_kb)/1024 avail_mb,
(seg.used_kb/ts.total_kb)*100 pct_used
from