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)

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

    

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;