Friday , July 21 2017
Home / Oracle DBA / Automatic Memory Management

Automatic Memory Management

Automatic Memory Management

The simplest way to manage instance memory is to allow the Oracle Database instance to automatically manage and tune it for you. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The total memory that the instance uses remains relatively constant, based on the value of MEMORY_TARGET, and the instance automatically distributes memory between the system global area (SGA) and the instance program global area (instance PGA). As memory requirements change, the instance dynamically redistributes memory between the SGA and instance PGA. Because the MEMORY_TARGET initialization parameter is dynamic, you can change MEMORY_TARGET at any time without restarting the database.  MEMORY _MAX _ TARGET, which is not dynamic.
Managing Table spaces
Compressed Table spaces
You can specify that all tables created in a table space are compressed by default. You specify the type of table compression using the DEFAULT keyword, followed by one of the compression type clauses used when creating a table. The following statement indicates that all tables created in the table space are to use OLTP compression, unless otherwise specified:
You can override the default table space compression specification when you create a table in that table space.
Encrypted Table spaces
You can encrypt any permanent table space to protect sensitive data. Table space encryption is completely transparent to your applications, so no application modification is necessary. Encrypted table spaces primarily protect your data from unauthorized access by means other than through the database. For example, when encrypted table spaces are written to backup media for travel from one Oracle database to another or for travel to an off-site facility for storage, they remain encrypted. Also, encrypted table spaces protect data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.
Table space encryption does not address all security issues. It does not, for example, provide access control from within the database. Any user who is granted privileges on objects stored in an encrypted table space can access those objects without providing any kind of additional password or key. When you encrypt a table space, all table space blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on. To maximize security, data from an encrypted table space is automatically encrypted when written to the undo table space, to the redo logs, and to any temporary table space. There is no need to explicitly create encrypted undo or temporary table spaces, and in fact, you cannot specify encryption for those table space types. For partitioned tables and indexes that have different partitions in different table spaces, it is permitted to use both encrypted and non-encrypted table spaces in the same table or index.
Table space encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create the encrypted table space and before you can store or retrieve encrypted data. When you open the wallet, it is available to all session, and it remains open until you explicitly close it or until the database is shut down.
Transparent data encryption supports industry-standard encryption algorithms ,including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:
The following statement creates an encrypted table space with the default encryption algorithm:
DATA FILE ‘/u01/app/oracle/or a data/orcl/secure01.dbf’ SIZE 100M
The following statement creates the same table space with the AES256 algorithm:
DATA FILE ‘/u01/app/oracle/or a data/orcl/secure01.dbf’ SIZE 100M
The following are restrictions for encrypted table spaces:
You cannot encrypt an existing table space with an ALTER TABLE SPACE statement.
Encrypted table spaces are subject to restrictions when transporting to another database.
When recovering a database with encrypted table spaces (for example after a SHUTDOWN ABORT or a catastrophic error that brings down the database instance),you must open the Oracle wallet after database mount and before database open, so the recovery process can decrypt data blocks and redo.
Recovering Tables Using Oracle Flashback Table
Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.
Oracle Flashback Table:
Restores all data in a specified table to a previous point in time described by a timestamp or SCN.
Performs the restore operation online.
Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.
Maintains any remote state in a distributed environment.
Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the FLASHBACK TABLE statement and another table that is not included in the FLASHBACK TABLE statement.
Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.
Note: You must be using automatic undo management to use Oracle Flashback features.
When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in are cycle bin, where it can later be recovered with the FLASHBACK TABLE statement if you find that you dropped the table in error. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:
DROP TABLE hr. admin_ emp PURGE;
Perhaps instead of dropping a table, you want to truncate it. The TRUNCATE statement provides a fast, efficient method for deleting all rows from a table, but it does not affect any structures associated with the table being truncated (column definitions, constraints, triggers, and so forth) or authorizations.
Using Flashback Drop and Managing the Recycle Bin
When you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how there cycle bin works, and how you manage its contents.
Caution: Before dropping a table, familiarize yourself with the consequences of doing so:
Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible.
All indexes and triggers associated with a table are dropped.
All views and PL/SQL program units dependent on a dropped table remain, yet become invalid (not usable).
All synonyms for a dropped table remain, but return an error when used.
All extents allocated for a table that is dropped are returned to the free space of the table space and can be used by any other object requiring new extents or new objects. All rows corresponding to a clustered table are deleted from the blocks of the cluster.
View More:
Overview of the System Global Area
ADR Structure
Overview of Diagnostic Files



Check Also


DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *