Saturday, December 13, 2014

Granting Privileges for Oracle Data Pump Operations in a Database Vault (DBV) Environment

Shown briefly in this post are the privileges that need to be granted to an Oracle User for performing Data Pump operations in a Database Vault (DV) enabled Environment.

Sign in as the DV Owner and run the following command to authorize Data Pump Operations for the user.
EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER(‘username’);

To authorize Transportable Tablespace operations, run the following command:
exec dbms_macadm.authorize_tts_user(uname,tsname);

References:

  • How To Export / Import Objects In Database Vault Environment (Doc ID 822048.1)

Tuesday, February 11, 2014

SQL Script to speed up Dropping an Oracle User/Schema by Truncating the underlying tables

Here is a SQL Script to speed up Dropping an Oracle User/Schema by Truncating/Dropping the underlying tables first:

     set linesize 255
      set pages 0
      set echo off
      set feedback off
      set term on
      set head off
      set verify off
      undefine which_user_p
      
      def which_user_p = &&which_user_p
      Prompt Generating the SQL script To Drop a User by Truncating the encapsulated Tables first
      
      set term off
      SPOOL DROP_USER_TRUNC_TABLES.sql
      SELECT 'TRUNCATE TABLE ' || OWNER ||'.'||OBJECT_NAME || ' ;'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&which_user_p')
      AND    OBJECT_TYPE = 'TABLE'
      UNION
      SELECT 'DROP TABLE ' || OWNER ||'.'||OBJECT_NAME || ';'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&which_user_p')
      AND    OBJECT_TYPE = 'TABLE'
      UNION
      SELECT 'DROP ' || OWNER ||'.'|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';'
      FROM   DBA_OBJECTS
      WHERE  OWNER = UPPER('&which_user_p')
      AND    OBJECT_TYPE IN ('PROCEDURE','PACKAGE','PACKAGE BODY', 'FUNCTION', 'SEQUENCE')
      order by 1 desc
      /      
      DROP USER &which_user_p CASCADE;
      SPOOL OFF


    

Wednesday, January 1, 2014

How to add and resize ASM Disks in an online fashion?

This is a post that covers the simple commands on how to add and resize Automatic Storage Management (ASM) disks to existing ASM Disk Groups.


Also, mentioned below are the commands with the Rebalancing Power clause: the higher the Rebalancing Power No, the more the system resources consumed (translates into system overhead but faster rebalancing).


Adding Disks to ASM Disk Groups (Without Rebalancing Power Clause):
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk51' SIZE 104200M;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk52' SIZE 104200M;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk53' SIZE 104200M;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk54' SIZE 104200M;



Resizing existing ASM Disks (Without Rebalancing Power Clause):
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk51' SIZE 204800M;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk52' SIZE 204800M;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk53' SIZE 204800M;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk54' SIZE 204800M;



Adding Disks to ASM Disk Groups (With Rebalancing Power Clause):
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk51' SIZE 104200M REBALANCE POWER 1024;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk52' SIZE 104200M REBALANCE POWER 1024;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk53' SIZE 104200M REBALANCE POWER 1024;
ALTER DISKGROUP DATA1 ADD DISK '/dev/asmd1disk54' SIZE 104200M REBALANCE POWER 1024;



Resizing existing ASM Disks (With Rebalancing Power Clause):
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk51' SIZE 204800M REBALANCE POWER 1024;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk52' SIZE 204800M REBALANCE POWER 1024;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk53' SIZE 204800M REBALANCE POWER 1024;
ALTER DISKGROUP DATA01 RESIZE DISK '/dev/asmd1disk54' SIZE 204800M REBALANCE POWER 1024;