TDE Data Dictionary

Tables Data Dictionary Views

The following views allow you to access information about tables.

View	Description
DBA_TABLES ALL_TABLES USER_TABLES	DBA view describes all relational tables in the database. ALL view describes all tables accessible to the user. USER view is restricted to tables owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.
DBA_TAB_COLUMNS ALL_TAB_COLUMNS USER_TAB_COLUMNS	These views describe the columns of tables, views, and clusters in the database. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZEstatement.
DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES	These views describe all relational and object tables in the database. Object tables are not specifically discussed in this book.
DBA_TAB_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS	These views display comments for tables and views. Comments are entered using the COMMENTstatement.
DBA_COL_COMMENTS ALL_COL_COMMENTS USER_COL_COMMENTS	These views display comments for table and view columns. Comments are entered using theCOMMENT statement.
DBA_EXTERNAL_TABLES ALL_EXTERNAL_TABLES USER_EXTERNAL_TABLES	These views list the specific attributes of external tables in the database.
DBA_EXTERNAL_LOCATIONS ALL_EXTERNAL_LOCATIONS USER_EXTERNAL_LOCATIONS	These views list the data sources for external tables.
DBA_TAB_HISTOGRAMS ALL_TAB_HISTOGRAMS USER_TAB_HISTOGRAMS	These views describe histograms on tables and views.
DBA_TAB_STATISTICS ALL_TAB_STATISTICS USER_TAB_STATISTICS	These views contain optimizer statistics for tables.
DBA_TAB_COL_STATISTICS ALL_TAB_COL_STATISTICS USER_TAB_COL_STATISTICS	These views provide column statistics and histogram information extracted from the relatedTAB_COLUMNS views.
DBA_TAB_MODIFICATIONS ALL_TAB_MODIFICATIONS USER_TAB_MODIFICATIONS	These views describe tables that have been modified since the last time table statistics were gathered on them. They are not populated immediately, but after a time lapse (usually 3 hours).
DBA_ENCRYPTED_COLUMNS USER_ENCRYPTED_COLUMNS ALL_ENCRYPTED_COLUMNS	These views list table columns that are encrypted, and for each column, lists the encryption algorithm in use.
DBA_UNUSED_COL_TABS ALL_UNUSED_COL_TABS USER_UNUSED_COL_TABS	These views list tables with unused columns, as marked by the ALTER TABLE ... SET UNUSEDstatement.
DBA_PARTIAL_DROP_TABS ALL_PARTIAL_DROP_TABS USER_PARTIAL_DROP_TABS	These views list tables that have partially completed DROP COLUMN operations. These operations could be incomplete because the operation was interrupted by the user or a system failure.

Example: Displaying Column Information

Column information, such as name, datatype, length, precision, scale, and default data values can be listed using one of the views ending with the _COLUMNS suffix. For example, the following query lists all of the default column values for the emp and dept tables:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, LAST_ANALYZED
    FROM DBA_TAB_COLUMNS
    WHERE OWNER = 'HR'
    ORDER BY TABLE_NAME;
    

The following is the output from the query:

TABLE_NAME           COLUMN_NAME          DATA_TYPE   DATA_LENGTH LAST_ANALYZED
-------------------- -------------------- ---------- ------------ -------------
COUNTRIES            COUNTRY_ID           CHAR                  2 05-FEB-03
COUNTRIES            COUNTRY_NAME         VARCHAR2             40 05-FEB-03
COUNTRIES            REGION_ID            NUMBER               22 05-FEB-03
DEPARTMENTS          DEPARTMENT_ID        NUMBER               22 05-FEB-03
DEPARTMENTS          DEPARTMENT_NAME      VARCHAR2             30 05-FEB-03
DEPARTMENTS          MANAGER_ID           NUMBER               22 05-FEB-03
DEPARTMENTS          LOCATION_ID          NUMBER               22 05-FEB-03
EMPLOYEES            EMPLOYEE_ID          NUMBER               22 05-FEB-03
EMPLOYEES            FIRST_NAME           VARCHAR2             20 05-FEB-03
EMPLOYEES            LAST_NAME            VARCHAR2             25 05-FEB-03
EMPLOYEES            EMAIL                VARCHAR2             25 05-FEB-03
.
.
.
LOCATIONS            COUNTRY_ID           CHAR                  2 05-FEB-03
REGIONS              REGION_ID            NUMBER               22 05-FEB-03
REGIONS              REGION_NAME          VARCHAR2             25 05-FEB-03

51 rows selected.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables014.htm#sthref1979 

Transparent Data Encryption Data Dictionary Views

The following data dictionary views maintain information about the encryption 
algorithms used to encrypt columns:




   




DBA_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS

Shows the algorithm used to encrypt columns for all tables that are 
accessible to a particular user.

• USER_ENCRYPTED_COLUMNS

  Shows the algorithm used to encrypt columns for all tables in a particular user's schema.
  
  
  
  

ALL_ENCRYPTED_COLUMNS
ALL_ENCRYPTED_COLUMNS displays encryption algorithm information for the encrypted columns in the tables accessible to the current user.
Related Views
DBA_ENCRYPTED_COLUMNS displays encryption algorithm information for all encrypted columns in the database.
USER_ENCRYPTED_COLUMNS displays encryption algorithm information for the encrypted columns in the tables owned by the current user. This view does not display the OWNER column.
Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
NOT NULL
Owner of the table
TABLE_NAME
VARCHAR2(30)
NOT NULL
Name of the table
COLUMN_NAME
VARCHAR2(30)
NOT NULL
Name of the column
ENCRYPTION_ALG
VARCHAR2(29)
 
Encryption algorithm used to protect secrecy of data in this column:
3 Key Triple DES 168 bits key
AES 128 bits key
AES 192 bits key
AES 256 bits key
SALT
VARCHAR2(3)
 
Indicates whether the column is encrypted with SALT (YES) or not (NO)

To check all encrypted table columns in the current database 
instance:
In SQL*Plus, select from the DBA_ENCRYPTED_COLUMNS view:
For example:
SELECT * FROM DBA_ENCRYPTED_COLUMNS;

This SELECT statement lists all tables and column in the 
database that contain columns encrypted using Oracle Transparent Data 
Encryption. For example:
OWNER        TABLE_NAME    COLUMN_NAME    ENCRYPTION_ALG     SALT
-----------  ----------    -----------    ----------------   ----
OE           CUSTOMERS     INCOME_LEVEL   AES 128 bits key   YES
OE           UNIT_PRICE    ORADER_ITEMS   AES 128 bits key   YES
HR           EMPLOYEES     SALARY         AES 192 bits key   YES

V$ENCRYPTION_WALLET
V$ENCRYPTION_WALLET displays information on the status of the 
wallet and the wallet location for transparent data encryption.

Column
Datatype
Description
WRL_TYPE
VARCHAR2(20)
Type of the wallet resource locator (for example, FILE)
WRL_PARAMETER
VARCHAR2(4000)
Parameter of the wallet resource locator (for example, absolute filename if WRL_TYPE = FILE)
STATUS
VARCHAR2(9)
Status of the wallet: OPEN / CLOSED / UNDEFINED

To check whether a wallet is open or closed

You can find out if a wallet is open or closed by running the V$ENCRYPTION_WALLET view.

To check whether a wallet is open or closed:

SELECT * FROM V$ENCRYPTION_WALLET;

The wallet status appears, similar to the following:
WRL_TYPE  WRL_PARAMETER                             STATUS
--------  ----------------------------------------  -------
file      C:\oracle\product\11.1.0\db_1\wallets     OPEN

To check the encrypted columns of an individual table

syntax.
DESC tablename;

For example:
DESC OE.ORDER_ITEMS;

A description of the table schema appears. For example:
Name                                      Null?     Type
----------------------------------------  --------  --------------------------
ORDER_ID                                  NOT NULL  NUMBER(12)
LINE_ITEM_ID                              NOT NULL  NUMBER(3)
PRODUCT_ID                                NOT NULL  NUMBER(6)
UNIT_PRICE                                          NUMBER(8,2)
QUANTITY                                            NUMBER(8) ENCRYPT



Securing Data : http://download.oracle.com/docs/cd/B28359_01/server.111/b28337.pdf
http://download.oracle.com/docs/cd/B28359_01/server.111/b28337/tdpsg_securing_data.htm