DBA SCRIPTS

1) DROP USER OBJECTS:

conn username/password --->>> MANDATORY to connect as a user
-- Becarefull while doing on prod

declare
cursor fkc is select table_name,
constraint_name
from user_constraints
where constraint_type ='R';
tname user_constraints.table_name%TYPE;
cname user_constraints.constraint_name%TYPE;
begin
open fkc;
loop
fetch fkc into tname, cname;
exit when fkc%NOTFOUND;
dbms_output.put_line('alter table '||tname||' drop constraint '||cname);
execute immediate 'alter table '||tname||' drop constraint '||cname;
end loop;
close fkc;

end;
/

declare
cursor fkc is select object_name,
object_type
from user_objects
where object_name not in
('INDEX','PACKAGE BODY');
obj_name user_objects.object_name%TYPE;
obj_type user_objects.object_type%TYPE;
begin
open fkc;
loop
fetch fkc into obj_name, obj_type;
exit when fkc%NOTFOUND;
dbms_output.put_line('Drop '||obj_type||' '||obj_name);
begin
 execute immediate 'Drop '||obj_type||' '||obj_name;
exception
 when others then null;
end;
end loop;
close fkc;
end;
/

PURGE RECYCLEBIN;

DECLARE

the_job user_jobs.job%TYPE;
cursor c1 is select job from user_jobs;
BEGIN
open c1;
loop
fetch c1 into the_job;
exit when c1%NOTFOUND;
dbms_job.remove(the_job);
end loop;
close c1;
END;
/

VERIFY :

     set heading off
SQL >   select 'Objects left in schema : ' from dual;
SQL>    select object_name,object_type from user_objects;

SQL>   select 'Jobs left in schema: ' from dual;
SQL>   select job,what from user_jobs;

2) User privileges & Grantee :

SELECT grantee, privilege, admin_option
FROM sys.dba_sys_privs
WHERE (privilege LIKE '% ANY %'
OR privilege IN ('BECOME USER', 'UNLIMITED TABLESPACE')
OR admin_option = 'YES')
AND grantee NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA')



3) Free Space :
//** This script lists all Tablespaces and its datafiles with their free and used space **//

SET SERVEROUTPUT ON

SET PAGESIZE 1000

SET LINESIZE 255

SET FEEDBACK OFF

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",

       Substr(df.file_name,1,40) "File Name",

       Round(df.bytes/1024/1024,2) "Size (M)",

       Round(e.used_bytes/1024/1024,2) "Used (M)",

       Round(f.free_bytes/1024/1024,2) "Free (M)",

       Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"

FROM   DBA_DATA_FILES  df,

       (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes

           FROM dba_extents  GROUP by file_id)  e,

       (SELECT Max(bytes) free_bytes, file_id

           FROM dba_free_space   GROUP BY file_id) f

WHERE  e.file_id (+) = df.file_id

AND    df.file_id  = f.file_id (+)

ORDER BY df.tablespace_name, df.file_name;

4) Tablespaces :

//** This script lists all Tablespaces with their Sizes **// 

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

5) Locked_objects :

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,

       NVL(b.oracle_username, '(oracle)') AS username,

       a.owner AS object_owner,

       a.object_name,

       Decode(b.locked_mode, 0, 'None',

                             1, 'Null (NULL)',

                             2, 'Row-S (SS)',

                             3, 'Row-X (SX)',

                             4, 'Share (S)',

                             5, 'S/Row-X (SSX)',

                             6, 'Exclusive (X)',

                             b.locked_mode) locked_mode,

       b.os_user_name

FROM   dba_objects a,

       v$locked_object b

WHERE  a.object_id = b.object_id

ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14

SET VERIFY ON

6) Data Pump Monitoring Script : 

select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode

from gv$session_longops sl, gv$datapump_job dp

where sl.opname = dp.job_name and sofar != totalwork;

7) RMAN Job Monitoring Script :

SQL >  SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

        FROM V$SESSION_LONGOPS

        WHERE OPNAME LIKE 'RMAN%'  AND OPNAME NOT LIKE '%aggregate%' 

              AND TOTALWORK != 0 AND SOFAR <> TOTALWORK ;

To see the Rman status and SID:

SQL >  SELECT s.SID, p.SPID, s.CLIENT_INFO FROM V$PROCESS p, V$SESSION s

       WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE 'rman%';

8) To see what USERS are Running :
SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address;

**** To see for a particular USER, what he is running *******

SELECT a.sid, a.serial#, a.username, b.sql_text FROM v$session a, v$sqlarea b
WHERE a.sql_address=b.address and a.username = '<username>';


9) Find FREE/USED/TOTAL size of oracle database :

(used space):

----------------

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments;

(free space):

---------------

SQL> select sum(bytes)/1024/1024/1024 GB from dba_free_space;

(Total database size):

---------------------------

SQL> select sum(bytes)/1024/1024/1024 GB from dba_data_files;

  +

(Temp size):

---------------

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

(or)

SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"
   FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a, (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
     FROM dba_temp_files) c;

10) RMAN Cold Backup :

Rman target / nocatalog

Rman > configure controlfile autobackup on;

Rman >  run {

2>  allocate channel d1 type disk;

3> Backup full tag full_offline_bkup

4> Format ‘/var/backup/corppsdb/rman/db_t%t_s%s_p%p’

5> Database plus archivelog;

6> Release channel d1;

}

Find more scripts here : http://www.oracle-base.com/dba/Scripts.php

Oracle Startup & Shutdown

Startup NOMOUNT

1) Control files are not read
2) Data files are not open
3) Background processes are started, SGA is allocated to Oracle by OS.
4) Instance is running by itself

Startup MOUNT

1) Oracle associates Instance with the database
2) Oracle opens and reads control files and gets names and location of data files and redo log files

Alter database mount
or
Startup Mount

Startup OPEN
1) Last stage of startup process.
2) Oracle reads all the data files and online redo logs, verifies that the database is consistent
3) If the files are not consistent, background process performs media recovery automatically.
4) Now all users can connect to database.

Alter database Open

Startup --> Mounts and Opens the database


Startup Restrict
1) No other users can connect to database other than you.
2) All new logins to the database are prevented
3) Existing logins will work.

Read Only

Startup Mount
Alter database open Read only


Shutdown or Shutdown Normal
1) No new user connections are allowed
2) Oracle waits for all users to exit their sessions, before shutting down the database.
3) No instance recovery is needed, as Oracle will write all redo log buffers to disk and database will be consistent
3) Oracle closes data files, terminates background process and sga is deallocated

Shutdown Transactional
1) Oracle waits for all active transactions to complete
2) Oracle will not wait for all users to log out their sessions.
3) New connections are not permitted, existing users cannot start new transaction
4) As long as no pending transactions, oracle will shutdown
5) No instance recovery is needed

Shutdown Immediate
1) All active transactions are rolled back
2) Disconnects all active users
3) This process is not immediate as it has to rollback all existing transactions
4) No new connections are permitted
5) No instance recovery needed

Shutdown Abort
1) Existing connections are terminated. Doesn't care about rolling back
2) Redo logs buffers are not written to disk
3) Upon restart, Oracle performs instance recovery.

Dropping Database
1) Connect as sys
2) Startup restrict mount
3) select name from v$database --> Just to check
4) Drop Database

DATA PUMP Scenarios

1)   Import 11g data dump into 10g

You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version

So, If your database is 11g and you want to export 11g and import into 10g

from 11g db ,

$  expdp  Test_schema_name/passs directory=datapump  schemas=Test_schema_name Version=10.2.0.4.0.

Once the export is done, you do the regular import from 10g server.



2)   Import multiple dump files

If the size of the dump file is large, usually they will be split into smaller chunks for easier ftp upload/download.

If you are trying to import a dump file that is split into many chunks, then you need to modify the DUMPFILE paratermeter to include %U

Ex:  If the dump files are named EXP_PROD_1.dmp, EXP_PROD_2.dmp etc ,    then  DUMPFILE=EXP_PROD_%U.DMP



3)   How to kill data pump jobs

When you import or export using data pump impdp or expdp commands, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too

Following sql will give you the list of data pump jobs

                   select * from dba_datapump_jobs

If you want to kill your impdp or expdp

1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y


If by mistake, you closed the window and your import/export job is still running,

1) Get the name of the job using
select * from dba_datapump_jobs

2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job

3) Once you are attached to job, Type Kill_Job
ex: Import> kill_job
Are you sure you wish to stop this job (y/n): y

And your job is killed, it will no longer show in dba_datapump_jobs


4) REUSE_DUMPFILE : ( Overwrite existing dumpfile)

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.

          $ expdp   scott/tiger   directory=exp_dir     dumpfile = x.dmp     table s= example    reuse_dumpfiles = y

Startup Restrict Mode

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict 

ORACLE instance started.

Total System Global Area 504366872 bytes

Fixed Size 743192 bytes

Variable Size 285212672 bytes

Database Buffers 218103808 bytes

Redo Buffers 307200 bytes

Database mounted.

Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;

system altered

SQL> alter system disable restricted session;

system altered

NOTE:   Find and disconnect users connected during restricted session. Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected

To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;

USERNAME LOGON_TIM PROCESS

----------------- ------------ -------------------

SYS      17-NOV-10       1310796

              17-NOV-10      1343899

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode, If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;

LOGINS

----------

RESTRICTED

ORA-01940: Cannot drop user that is currently connected

SQL> drop user username cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

Solution 

Make sure the user is logged out, then re-execute the command.

If you are permitted to kill the session of that user then find out the connected user sid and serial# by,

SQL> select sid, serial# from v$session where username = 'USERNAME';

   SID    SERIAL#                
---------- ----------
   268       1268
   315       1223

If RAC use GV$SESSION view to get instance#

NOTE:   Before killing session you may wish to lock the account for further connection attempts. This is extremely necessary to drop users who automatically establish session like to drop an application user or to drop a user who performs batch jobs.

SQL> Alter user username account lock;

Now kill the connected session.

SQL> alter system kill session '268,1268';           (use @instance# if RAC db)
       System altered

SQL> alter system kill session '315,1223';
      System altered

And then drop the user.

SQL> drop user username cascade;
          User dropped

Moving a table from one tablespace to another

There are couple of ways in which a table can be moved to a different tablespace:

   a) One of them is to perform export/import 

   b) Another is to use ‘ALTER TABLE’ command with ‘MOVE tablespace’ clause

If you move a table from one tablespace to another, you need to REBUILD the Related indexes if those indexes are on same tablespace

      1)  Check indexes for a table

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status

-----------------       ------------------       ----------

Ind_name            tab_name            valid

2)     Check the tablespace in which our table is located

SQL> select tablespace_name,table_name from user_tables where table_name=’TAB_NAME’;

3)    Now moving to another tablesapce

SQL>  ALTER TABLE tab_name MOVE TABLESPACE new_tablespace;

4)    Now check the status of the index again

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status

-----------------       ------------------       ----------

Users                 tab_name             Unusable

                                                                              

5)    Rebuild the index in order to make the index valid

SQL>  ALTER INDEX ind_name REBUILD;

 index altered

6)    Check the status of the index again

SQL> SELECT INDEX_NAME, TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME = ‘ tab_name’;

Index_name       table_name           status

-----------------       ------------------       ----------

ind_name               tab_name           valid

Manually uninstalling oracle

Windows

In the past I've had many problems uninstalling all Oracle products from Windows systems. Here's my last resort method:

Uninstall all Oracle components using the Oracle Universal Installer (OUI).

Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.

Delete any references to Oracle services left behind in the following part of the registry:

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*

It should be pretty obvious which ones relate to Oracle.

Reboot your machine.

Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.

Delete the "C:\Program Files\Oracle" directory.

Empty the contents of your "c:\temp" directory.

Empty your recycle bin.

At this point your machine will be as clean of Oracle components as it can be without a complete OS reinstall.

Remember, manually editing your registry can be very destructive and force an OS reinstall so only do it as a last resort.

If some DLLs can't be deleted, try renaming them, the after a reboot delete them.

                                                                  ( OR )

Oracle11g introduce new utility "deinstall" to uninstall/deinstall oracle database with configuration files.

run deinstall.bat file $ORACLE_HOME/deinstall/deinstall.bat

and give the prompt details then system will remove all the related files/services/folder.

if oci.dll not delete during deinstall process then restart system and delete manually.

UNIX

Uninstalling all products from UNIX is a lot more consistent. If you do need to resort to a manual uninstall you should do something like:

Uninstall all Oracle components using the Oracle Universal Installer (OUI).

Stop any outstanding processes using the appropriate utilities:

#  oemctl  stop  oms  user/password

#  agentctl  stop

#  lsnrctl  stop

Alternatively you can kill them using the kill -9 pid command as the root user.

Delete the files and directories below the $ORACLE_HOME:

#  cd   $ORACLE_HOME

#  rm  -Rf  *

With the exception of the product directory, delete directories below the $ORACLE_BASE.

#  cd  $ORACLE_BASE

#  rm  -Rf  admin  doc  jre  o*

Delete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.

                  #  rm  /etc/oratab /etc/emtab