oracle

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing … [Read more...] about How to reclaim entire space of an oracle database table with “Truncate Table” statement

This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace. This can serve as an early warning system to prevent database space allocation crises. Note that no attempt is made to coalesce adjacent fragments. It is thus possible sometimes to obtain an extent larger than the size reported as the Largest Free Frag. Since a large space-consuming … [Read more...] about Oracle SQL Script to Report Tablespace Free and Fragmentation

Question: If we want to truncate any large table and reclaim space, which is the truncate command out of below 3 which can be used? TRUNCATE TABLE table_name; OR TRUNCATE TABLE table_name DROP STORAGE; OR TRUNCATE TABLE table_name REUSE STORAGE; REUSE STORAGE VS DROP STORAGE Here is the difference between drop and reuse storage: DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of … [Read more...] about Truncate Table Statement: REUSE STORAGE VS DROP STORAGE

This post explains the steps needed to clone a Pluggable Database (PDB) when either Source or Target or both uses Oracle Key Vault (OKV). Case 1: When both Source and Target databases are using Oracle Key Vault (TDE Direct connection) 1. Create the PDB in the Target container by cloning the Source PDB (whichever method customer feels suitable). 2. Find the currently Active Masterkey corresponding to the Source PDB (from v$encryption_keys) and Add Target DB's Wallet as a Member of this … [Read more...] about How to Clone PDB With Oracle Key Vault (OKV)

Oracle Key Vault Administrators Separation of administrative duties is required for secure systems. Oracle Key Vault distinguishes between key, system, and audit management functions. The corresponding roles for these functions are system administrator, key administrator, and audit manager. If desired, one user can be granted multiple roles. However, for separation of duties, it is recommended that different users have different administrative roles. This would enable one administrator to … [Read more...] about What are Oracle Key Vault Roles

Oracle Key Vault is a central, secure key, management platform. It centrally manages encryption keys such as Oracle wallets, Java keystores, and credential files, and is optimized for Oracle Advanced Security Transparent Data Encryption (TDE) master keys. In other words, Oracle Key Vault is a turnkey solution that is based on a hardened stack. It is easy to install, configure, deploy, and patch. Oracle Key Vault includes separation of duties for administrative users, full auditing, … [Read more...] about What Is Oracle Key Vault

Required Privileges To log in to Cloud Control, a user must be granted the DV_OWNER, the DV_ADMIN, or the DV_SECANALYST role. To create a security officer account that has privileges to view reports, but does not have the rights to change the Database Vault configuration, perform the following: 1. As the Database Vault Account Manager, create the security officer user: CONNECT bea_dvacctmgr/[password] CREATE USER sec_rep IDENTIFIED BY [password]; GRANT CONNECT TO sec_rep; 2. As the … [Read more...] about Auditing with Oracle Database Vault Reports

Secure Application Roles A secure application role is a database role that can be enabled by using only a specific, declared PL/SQL procedure. This procedure is usually written by an application developer. In Database Vault, the procedure is implemented by the Database Vault packages. Database Vault changes the implementation of secure application roles to ease their administration, development, and use. You can create secure application roles that are enabled based on the outcome of a rule … [Read more...] about Oracle Database – Configuring Secure Application Roles

Using Factors Factors are evaluated at the session-level. Each session that accesses the factor can have a different identity (value). For example, the Client_IP factor evaluates to the IP address of the client machine for each session. You can use factors for activities, such as authorizing database accounts to connect to the database or creating filtering logic to restrict the visibility and manageability of data. Database Vault provides a selection of factors that enable you to set … [Read more...] about Extend rule sets by using factors in Oracle Database Vault