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
(select ts.tablespace_name,ts.status,sum(df.bytes)/1024 total_kb
from dba_tablespaces ts,dba_data_files df
where ts.tablespace_name = df.tablespace_name
group by ts.tablespace_name,ts.status) ts,
(select ts.tablespace_name,ts.status,nvl(sum(seg.bytes)/1024,0) used_kb
from dba_tablespaces ts,dba_segments seg
where ts.tablespace_name = seg.tablespace_name (+)
group by ts.tablespace_name,ts.status) seg
where
ts.tablespace_name = seg.tablespace_name
order by
seg.used_kb/ts.total_kb;

alter tablespace USER_DATA add datafile ‘ /oradata/fs89/etm01p/user_data449.dbf ’ size 512m;

 

select file_id,file_name from dba_data_fileswhere tablespace_name=<name> ;

DB Link Create

create database link "d1vpm1d1.db.att.com"

     connect to vpmo

     identified by "xxxxxx"

     using ' d1vpm1d1.db.att.com ';

create database link

OFAR200.SBC.COM

connect to M59886 identified by A9B9$9C9 using 'ofar200.sbc.com';

And try again with the following query:

select * from ALL_WEB_PHONE_VIEW@OFAR200.SBC.COM;