General administration commands

1)How to set oracle home in the windows env...??

D:\>set
it will show all the option
        Then
set ORACLE_HOME=F:\oracle\product\10.1.0

        Note:path is depends on system to system

2)How to check overall data space...??

         col "Database Size" format a20
         col "Free space" format a20
         select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
          , round(free.p / 1024 / 1024) || ' MB' "Free space"
          from (select bytes from v$datafile
          union all
          select bytes from v$tempfile
          union all
          select bytes from v$log) used
          , (select sum(bytes) as p from dba_free_space) free
          group by free.p

3)How to check state of database..??

         select open_mode from v$database;

4)How check Archive log mode..???

         select open_mode from v$database;

5)How to check alert log...??

         https://www.youtube.com/watch?v=wBu9zWT5TpU

6)What is pfile & spfile..??

        https://www.youtube.com/watch?v=hglewqw7WJw

7)To check free Space in Tablespace.

 SQL> select tablespace_name,round (sum(bytes)/1024/1024) Free_Space_MB from dba_free_space group by (TABLESPACE_NAME);

OR


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
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

8)To add datafile in Tablespace.

alter tablespace tablespacename add datafile 'F:\ORACLE\ORADATA\AEI\datafilename.DBF' size 100M;


9)To get AWR report.

 @$ORACLE_HOME/rdbms/admin/awrrpt.sql
 @$ORACLE_HOME/rdbms/admin/awrrpti.sql


10)To delete archive log with respect to sysdate from RMAN prompt

delete noprompt archivelog until time 'SYSDATE-5' ;

OR

RMAN>delete archivelog until sequence 61680;

11)To get details of temporary tablespace

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

12)To get details of about the tablespace

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb,(tbs_size - free_space) as usedmb,round(100 * ( (tbs_size - (tbs_size - free_space) )/ tbs_size))

"Pct. Free"

 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

UNION

select tablespace_name, sum(bytes)/1024/1024 tbs_size

from dba_temp_files

group by tablespace_name ) b

where a.tablespace_name(+)=b.tablespace_name;

13)To get Backup status of RMAN

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,

elapsed_seconds/3600                   hrs

from V$RMAN_BACKUP_JOB_DETAILS order by START_TIME desc;

14)To get the query used while creation of table

SQL> set long 1000

SQL> set pagesize 0

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

15)Query to know no of sessions operating on particular table.

column username  format a10  heading "Username"

column terminal heading Term format a6

 column tab format a35 heading "Table Name"

 column owner format a9

 column Address format a18

 column ctime heading "Seconds"

select     nvl(S.USERNAME,'Internal') username,

        nvl(S.TERMINAL,'None') terminal,

              L.SID||','||S.SERIAL# Kill,

       U1.NAME||'.'||substr(T1.NAME,1,20) tab,

       decode(L.LMODE,1,'No Lock',

               2,'Row Share',

               3,'Row Exclusive',

               4,'Share',

               5,'Share Row Exclusive',

              6,'Exclusive',null) lmode,

     decode(L.REQUEST,1,'No Lock',

              2,'Row Share',

              3,'Row Exclusive',

            4,'Share',

                5,'Share Row Exclusive',

                6,'Exclusive',null) request,

           l.ctime

   from       V$LOCK L,

      V$SESSION S,

      SYS.USER$ U1,

      SYS.OBJ$ T1

   where      L.SID = S.SID

   and        T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)

   and        U1.USER# = T1.OWNER#

and     S.TYPE != 'BACKGROUND'

   order by 1,2,5

  /
16)To go to open state from restricted one and vice-versea.
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM ENABLE RESTRICTED SESSION;