DBA SCRIPTS

1) DROP USER OBJECTS:

conn username/password --->>> MANDATORY to connect as a user

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 Database Link to MS Access

Oracle Database Link to MS Access

In this post, I describe how you can make an Oracle database link to an MS Access database.
When done, you can query from an oracle database onto an Access database.
 But with some little changes, you can do the same trick to a SQL Server database or even a
database type you like (to which you can connect by ODBC).
Important: The init-parameter GLOBAL_NAMES should have value FALSE. Check this parameter first
 before continuing the procedure.
In this example, I uses TESTDB as database name, replace this with your database/name/user id.
 If I mention %oracle_home%, I mean the Oracle Home directory.
This example is worked out on an Oracle 8.1.7.4 installation,
 but I suppose it will work on higher Oracle versions too.

1. Create a new ODBC link

1. On the Oracle server start the ODBC Data Source Administrator. This can be done by using the run command: go to start/run and type odbcad32 and press enter.
2. In the ODBC Data Source Administrator window go to the System DSN tab and click on Add...
3. Choose in the Create New Data Source window the Microsoft Access Driver (*.mdb) and click on Finish.
4. In the ODBC Microsoft Access Setup window enter the data source name (e.g. TESTDB), the description (e.g. Test database) and select the Access database
   1. If the Access database is on the same machine as Oracle is running, you can ignore step 1.5 and step 2!
5. If the Access database is on another machine in the network, you should use UNC-path notation. In the Map Network Drive window, leave the drive-letter on (none) and select via the UNC-path the Access database. Finish the creation of the ODBC Link and close all the ODBC windows.

2. Create Network ID

In order to perform a good connection to the Access database on another machine, you need to follow the following steps:

1. Create on the network an user id (e.g. TestDbAdmin) and give this UID read rights on the location of the Access files. NOTE: use the appropriate rights, this depends on the actions you want to take on the Access database.
2. Place this user, TestDbAdmin, in the ORA_DBA group on the Oracle server.

3. Create the Oracle TestDb listener

In these steps, some Oracle files will be created and/or modified. A new listener will be created, which will take care of the connection
1. Open the file inithsodbc.ora (%oracle_home%/hs/admin) and save this as initTESTDB.ora. Edit the content so it looks like:

01.# This is a sample agent init file that contains the HS parameters that

02.# are needed for an ODBC Agent.

03.#

04.# HS init parameters

05.#

06.HS_FDS_CONNECT_INFO = HORSODBC

07.HS_FDS_TRACE_LEVEL = OFF

08.#

09.# Environment variables required for the non-Oracle system

10.#

11.#set <envvar>=<value>

2. Open the file listener.ora (%oracle_home%/network/admin) and add the following text, save and close the file:

01.LISTENERTESTDB =    

02.(ADDRESS_LIST=     

03.(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))     

04.(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))     

05.)     

06.SID_LIST_LISTENERTESTDB=     

07.(SID_LIST=     

08.(SID_DESC=     

09.(SID_NAME=TESTDB)     

10.(ORACLE_HOME = C:\oracle\ora81)     

11.(PROGRAM=hsodbc)     

12.)     

13.)

3. Open the file tnsnames.ora (%oracle_home%/network/admin) and add the following text, save and close the file:

1.TESTDB =    

2.(DESCRIPTION =     

3.(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1522))     

4.(CONNECT_DATA =     

5.(SID = TESTDB)     

6.)     

7.(HS = OK)     

8.)

4. Check the file sqlnet.ora (%oracle_home%/network/admin) if the following setting is correct (correct if it differs):

1.SQLNET.AUTHENTICATION_SERVICES= (NONE)

5. Start the new TESTDB listener with the following command (on the command line tool):

NOTE: DON'T USE A RDP (REMOTE DESKTOP) CONNECTION! Because of windows credential error. Apply this directly on the Oracle machine or via e.g. VNC Viewer connection.

Orahome\bin\lsnrctl start listenerTestDb
Starting the new listener can produce an error message, because the services uses the default logon settings (system account).
6. Configure the ListenerTestDb:

• Open the properties panel of this service (can be found in the windows services list at the administrative tools)
• Stop the service (if it is running) and edit the log on credentials to the user account earlier made (e.g. TestDbAdmin)
• (Re)start the service.

7. Test the listener with the following statement:

1.%oracle_home%\bin\tnsping testdb

If the test succeeded, the database link in Oracle can be made.

4. Create Oracle Database Link to TestDb

1. Create database link to TESTDB (note: credentials SYSTEM/MANGER can be changed to your configuration):

1.CREATE PUBLIC DATABASE LINK “TESTDB”    

2.CONNECT TO "SYSTEM"     

3.IDENTIFIED BY "MANAGER"

4.USING 'TESTDB'

2. Test the link with a count(*) query to a table on the TESTDB database.
If you got a number of rows back, you're done!