Oracle Table Space Fundamentals (2)

One, compressed table space

The compressed tablespace is implemented by compressing objects within the table space. The compression of data is reflected in the data block, which is simply to reduce I/O and improve performance performance by clearing the duplicate content on the data block. By specifying the DEFAULT keyword in a table space's creation statement, you can compress all the tables created on that tablespace (in effect, compression occurs when data is inserted).

Table space compression types can be classified into OLTP compression, unstructured file copy cleanup, unstructured file compression, and backup data compression, depending on the table space where the data objects are stored. Where OLTP compression refers to the compression that occurs during DML operations (including insert,delete,update). A compressed table can be stored in fewer chunks, saving storage space, and using fewer blocks of data means better performance. Querying a compressed table in an environment where I/O is constrained can often be done more quickly because they need to scan a much smaller chunk of the database data.

# Create default Compressed table space sql> create tablespace test datafile ' D:\app\oracle\oradata\orcl\test01.dbf '  2 default  compress For OLTP, the tablespace has been created. # Modify compressed tablespace sql> alter tablespace test default nocompress; the tablespace has changed. # Determine if a tablespace is compressed sql> select Tablespace_name,def_tab_compression from Dba_tablespaces; Tablespace_name                                              def_tab_compress-------------------------------------------------------------------------- --system                                                       disabledsysaux                                                       DISABLEDUNDOTBS1                                                     disabledtemp                                                         disabledusers                                                        disabledexample                                                      Disabledtest                                                         enabled has selected 7 rows.

　　

Second, encrypted table space

Encryption techniques can be used to protect sensitive data on a permanent table space. The encryption technology for TABLESPACE is completely transparent to the user, whose primary purpose is to protect data from unauthorized access outside of the database, such as accessing data files directly from the file system of the operating system. However, tablespace encryption does not address all security issues, for example, it does not provide access control technology for database access content, and any user with permission can access and store objects in the table space without having to provide an additional password or secret key.

When a table space is encrypted, all data blocks within the tablespace are encrypted, and all segment types support encryption, including table segments, index segments, and so on. To provide maximum security, data from the encrypted tablespace is still encrypted when it is written to the undo tablespace, the temporary tablespace, without the need to create an encrypted undo tablespace and an encrypted temporary table space. In fact, we cannot create such a table space.

Common table space encryption algorithms are: aes128,aes192,aes256,3des168.

The following issues should be considered when using encrypted tablespace:

• Unable to encrypt the existing table space.
• Encrypted tablespace receives a limit when it makes a tablespace transfer.
• Open Oracle Wallet on backup and restore.

Using tablespace encryption technology to create an Oracle Wallet, for example in my Windows environment, create the following method:

# Specify Oracle Wallet directory, d:\app\oracle\admin\orcl\wallet# edit D:\app\oracle\product\11.2.0\dbhome_1\database\ The Sqlnet.ora file, which describes how the wallet is implemented and the physical location, restarts the database after completion. Encryption_wallet_location= (Source= (Method=file) (Method_data= (Directory=d:\app\oracle\admin\orcl\wallet))) # Create an encrypted file under Wallet sql> alter system set encryption key identified by "Oracle"; the system has changed. # Open and Close Oracle Wallet, after creating Wallet on Windows Wallet is turned on by default sql> alter system set encryption Wallet open identified by "Orac Le "; the system has changed. Sql> alter system set encryption wallet close identified by "Oracle"; Sql> select * from V$encryption_wallet; Wrl_type wrl_parameter STATUS---------------------------------------------------File D:\AP P\oracle\admin\orcl\wallet open# Create an encrypted tablespace sql> create tablespace encrypt_tbs 2 datafile ' D:\APP\ORACLE\ORADATA\ORCL   \SECUREFILE.DBF ' reuse 3 encryption using ' AES192 ' 4 default storage (encrypt); The tablespace has been created. # View tablespace encryption sql> Select T.NAME,E.ENCRYPTIONALG algorithm 2 from V$tablespAce T,v$encrypted_tablespaces E 3 where T.ts#=e.ts#;name Algorit                                                  HM--------------------------------------------------------------------------Encrypt_tbs AES192

Oracle Table Space Fundamentals (2)