How to encrypt a table using dbm_redef with zero down time

We can encrypt a table using simple alter table command, However alter table command on big tables takes time and during this process, transaction will be blocked on that table. So best solution to avoid downtime is to use dbms_redef method to move the table to a new encrypted tablespace.   ORIGINAL TABLE_NAME – > […]

orapwd tool for password file in oracle

The password file stores a list of usernames and passwords that are allowed to remotely authenticate as SYSDBA over the network. Oracle must use this file to authenticate them, not the normal list of passwords stored in the database. And for this authentication to use password file, the value of REMOTE_LOGIN_PASSWORDFILE should be EXCLUSIVE or […]

PDB Lockdown Profiles in Oracle 12.2

Use the ALTER LOCKDOWN PROFILE statement to alter a PDB lockdown profile. You can use PDB lockdown profiles in a multitenant environment to restrict user operations in pluggable databases (PDBs). Pluggable databases bring a new separation of database administrator roles. The DBA administers the container database, the CDB, but can then delegate the administration of […]

Lock account automatically with INACTIVE_ACCOUNT_TIME

In Oracle 12.2 Release We can use the INACTIVE_ACCOUNT_TIME resource parameter in profile to automatically lock the account of a database user who has not logged in to the database instance in a specified number of days. 1. By default, it is set to UNLIMITED. 2. The minimum setting is 15 and the maximum is […]

Unified audit trail in Oracle 12c

OVERVIEW:                 With oracle 12c, unified auditing has been introduced. It consolidates all audit trails into a single audit trail table. It will capture audit records from below sources. SYS audit records ( which was written to os trail in traditional method, will now be written to db […]

Purge AUD$ table using DBMS_AUDIT_MGMT

            Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier. Follow below steps for puring aud$ table. 1. Make sure AUD$ table is not […]

ENABLE_DDL_LOGGING in oracle 12c

This  ENABLE_DDL_LOGGING parameter has been introduced in oracle 12c. If this ENABLE_DDL_LOGGING is enabled,then DDL records are written to the ADR. All DDL operations like alter/create/drop/truncate objects. Only drop user will be logged, But create user will not be. Enable the parameter: SQL> show parameter enable_ddl_logging NAME TYPE VALUE ———————————— ———– —————————— enable_ddl_logging boolean FALSE SQL> […]

How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT

If your AUD$ table is in SYSTEM and SYTEM tablespace, Then it is advised to move the AUD$ to a dedicated tablespace. Use below steps to move AUD$. select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name=’AUD$’ OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ——- ————- —————— —————————— ————— SYS AUD$ TABLE SYSTEM 16 Use the dbms_audit_mgmt to move the […]

how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c

DBMS_PRIVILEGE_CAPTURE: ————————- Oracle 12c introduced the DBMS_PRIVILEGE_CAPTURE package, which helps us in doing privilege analyze and find report on used and unused privileges. In order to use the DBMS_PRIVILEGE_CAPTURE package you must be granted the CAPTURE_ADMIN role. steps involve: —————- CREATE_CAPTURE ENABLE_CAPTURE DISABLE_CAPTURE( after waiting for necessary time) GENERATE_RESULT DROP_CAPTURE Though there are 4 options […]

Open wallet automatically after starting the database

If encryption wallet is enabled, then everytime we start the database, we have to open the wallet manually. So to avoid this, we can create one trigger which will open the wallet automatically , once you start the database. Below is the trigger:   CREATE or replace TRIGGER OPEN_WALLET AFTER STARTUP ON DATABASE BEGIN –execute […]