expdp exclude schema or table

expdp export exclude syntax:

Here is a simple example on how to use exclude in your export cmds.

----------------------------------------------
Exclude Tables
----------------------------------------------

Using parfile:

First create a parfile with details.
$ vi impdp_full.par

--add the details to parfile
directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4  EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3')

Then execute using parfile:
expdp SCOTT/XXXXXX parfile=impdp_full.par

on cmdline:
expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT  parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"

 

----------------------------------------------
Exclude Schemas
----------------------------------------------

First create a parfile with details.

$ vi expdp_full.par
--add the details to parfile
directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.log   parallel=6 EXCLUDE=SCHEMA:"IN ('SCHEMA1','SCHEMA2','SCHEMA3')

Then execute using parfile:
expdp SYSTEM/XXXXXX parfile=expdp_full.par

on cmdline:
expdp SYSTEM/XXXXX directory=DPUMP dumpfile=FULLDB_%U.dmp logfile=FULL.log FULL=Y  parallel=6 EXCLUDE=TABLE:\"IN (\'SCHEMA1\', \'SCHEMA2\')\"

Setting up Optach environment variable

Setting up Optach environment variable :

For Korn / Bourne shell:

% export PATH=$PATH:$ORACLE_HOME/OPatch

For C Shell:

% setenv PATH $PATH:$ORACLE_HOME/OPatch

Simple password encryption package to demonstrate how

rem -----------------------------------------------------------------------
rem Purpose:   Simple password encryption package to demonstrate how
rem                  values can be encrypted and decrypted using Oracle's
rem                  DBMS Obfuscation Toolkit
rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------


---- create table to store encrypted data

-- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator.
CREATE TABLE USERS_INFO
(
  USERNAME VARCHAR2(20 BYTE)
, PASS VARCHAR2(20 BYTE)
)users;

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

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;


--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

package to encrypt data inside database using DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT

rem -----------------------------------------------------------------------
rem Purpose:   Simple password encryption package to demonstrate how
rem                  values can be encrypted and decrypted using Oracle's
rem                  DBMS Obfuscation Toolkit
rem Note:        Connect to SYS AS SYSDBA and run ?/rdbms/admin/catobtk.sql
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------


---- create table to store encrypted data

-- Unable to render TABLE DDL for object ATOORPU.USERS_INFO with DBMS_METADATA attempting internal generator.
CREATE TABLE USERS_INFO
(
  USERNAME VARCHAR2(20 BYTE)
, PASS VARCHAR2(20 BYTE)
)users;

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

CREATE OR REPLACE PACKAGE PASSWORD AS
   function encrypt(i_password varchar2) return varchar2;
   function decrypt(i_password varchar2) return varchar2;
END PASSWORD;
/
show errors


CREATE OR REPLACE PACKAGE BODY PASSWORD AS

  -- key must be exactly 8 bytes long
  c_encrypt_key varchar2(8) := 'key45678';

  function encrypt (i_password varchar2) return varchar2 is
    v_encrypted_val varchar2(38);
    v_data          varchar2(38);
  begin
     -- Input data must have a length divisible by eight
     v_data := RPAD(i_password,(TRUNC(LENGTH(i_password)/8)+1)*8,CHR(0));

     DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(
        input_string     => v_data,
        key_string       => c_encrypt_key,
        encrypted_string => v_encrypted_val);
     return v_encrypted_val;
  end encrypt;

  function decrypt (i_password varchar2) return varchar2 is
    v_decrypted_val varchar2(38);
  begin
     DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(
        input_string     => i_password,
        key_string       => c_encrypt_key,
        decrypted_string => v_decrypted_val);
     return v_decrypted_val;
  end decrypt;


end PASSWORD;
/
show errors

-- Test if it is working...

select password.encrypt('PASSWORD1') from dual;
select password.decrypt(app_password.encrypt('PASSWORD1')) from dual;
select password.encrypt('PSW2') from dual;
select password.decrypt(app_password.encrypt('PSW2')) from dual;


--- Insert encrypted Password ---

insert into USERS_INFO values ('redddy',( select password.encrypt('REDDY1') from dual) );
select password.decrypt((pass)) from USERS_INFO where USERNAME='redddy';

How to create multiple loops in plsql procedure

declare
        cursor c_job is select * from job;
        r_job c_job%ROWTYPE;
        cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;

        r_emp c_empInjob%ROWTYPE;
    begin
        open c_job;
        loop
           fetch c_job into r_job;
           exit when c_job%NOTFOUND;
           open c_empInjob (r_job.empno);
           loop
               fetch c_empInjob into r_emp;
               exit when c_empInjob%NOTFOUND;
           end loop;
           close c_empInjob;

       end loop;
       close c_job;
   end;
   /



 reference :
 http://www.java2s.com/Tutorial/Oracle/0500__Cursor/Placingcursorsinnestedloops.htm

update rows from multiple tables (correlated update)

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL.
In this article, we are going to look at four scenarios for Oracle cross table update.

Suppose we have two tables Categories and Categories_Test. See screenshots below.

lets take two tables TABA & TABB:

Records in TABA:

Records in TABB:

1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B.

The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.

 update TABA A
set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID);

2. Update data in two columns in table A based on a common column in table B.

If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);

How to Secure our Oracle Databases

How Secure can we make our Oracle Databases??

This is a routine question that runs in minds of most database administrators.  

HOW SECURE ARE OUR DATABASES. CAN WE MAKE IT ANYMORE SECURE.

I am writing this post to share my experience and knowledge on securing databases. I personally follow below tips to secure my databases:

 1. Make sure we only grant access to those users that really need to access database.

2. Remove all the unnecessary grants/privileges from users/roles.

3. Frequently audit database users Failed Logins in order to verify who is trying to login and their actions.

4. If a user is requesting elevated privileges, make sure you talk to them and understand their requirements.

5. Grant no more access than what needed.

6. At times users might need access temporarily. Make sure these temporary access are revoked after tasks are completed.

7. Define a fine boundary on who can access what??

8. Use User profiles / Audit to ensure all activities are tracked.

9.  Enforce complex password. Here is the Link on how to do it 

10 Use Triggers to track user activity.

11. Make sure passwords are encrypted in applications, this can be potential threat if you application code has been compromised.

12. Add password to your listener.

13. Allow access only from needed/known servers/clients. Use Valid_node_checking Link on how to restrict access to servers/clients.