1 Create Additional Buffer Cache

       1.1 set up the db_16k_cache_size (Then using the 16K cache to create tablespace) Sga 16k_size 15m

    2. Administer partitioned tables and Manage encrypted tablespaces

        2.1 Create a reference partition table

               2.1.1. new_orders is range partition table,You need to create table order_items using reference partition

               2.1.2. The order_items table contains such as the following:

                         order_id number(12) not null,

                         line_item_id number(3) not null,

                         product_id number(6) not null,

                         unit_price number(8,2),

                         quantity number(8)

               2.1.3. The new_orders table partition using tablespace tbs1 and tbs2, requirements the order_items table partition refer to new_orders table partition tablespace consistency

               2.1.4. Execute the script, insert data into the partition table, as follows::

                         insert into new_orders values(101,to_date('2005-02-03','yyyy-mm-dd'),'x1',1001,1,20000,88001,88002);

                         insert into new_orders values(102,to_date('2005-05-15','yyyy-mm-dd'),'x1',1002,1,30000,88002,88002);

                         insert into new_orders values(103,to_date('2005-09-22','yyyy-mm-dd'),'x1',1003,1,40000,88003,88002);

                         insert into new_orders values(104,to_date('2005-12-30','yyyy-mm-dd'),'x1',1004,1,10000,88004,88002);

                         insert into order_items values(101,801,200001,200,100);

                         insert into order_items values(102,802,200002,300,100);

                         insert into order_items values(103,803,200003,400,100);

                         insert into order_items values(104,804,200004,100,100);

                         commit;

实验准备

        2.2 Create interval partition table

              2.2.1. Execute the script to create 4 tablespace, the script follows::

                        create tablespace SALES_TBS1 datafile '/u01/app/oracle/oradata/PROD1/sales_tbs1_01.dbf' size 10m autoextend on;

                        create tablespace SALES_TBS2 datafile '/u01/app/oracle/oradata/PROD1/sales_tbs2_01.dbf' size 10m autoextend on;

                        create tablespace SALES_TBS3 datafile '/u01/app/oracle/oradata/PROD1/sales_tbs3_01.dbf' size 10m autoextend on;

                        create tablespace SALES_TBS4 datafile '/u01/app/oracle/oradata/PROD1/sales_tbs4_01.dbf' size 10m autoextend on;

              2.2.2. Partition table has 4 range partitioning, respectively contained 2002,2003,2004,2005 data, and the 4 partition tablespace is respectively the 4 tablespace in front of the establishment;

              2.2.3. the partition is interval of 1 years;

              2.2.4. The name of the table is newsales, partitioning key is time_id, contains a column as follows:

                         prod_id number(6),

                         cust_id number,

                         time_id date,

                         channel_id char(1),

                         promo_id number(6),

                         quantity_sold number(3),

                         amount_sold number(10,2)

              2.2.5. Execute the script, insert data into the partition table, scripts are as follows:

                        insert into sh.newsales values(908001,101,to_date('2002-8-10','yyyy-mm-dd'),'a',88001,100,200);

                        insert into sh.newsales values(908002,102,to_date('2003-7-10','yyyy-mm-dd'),'a',88002,100,800);

                        insert into sh.newsales values(908003,103,to_date('2004-5-30','yyyy-mm-dd'),'a',88003,100,700);

                        insert into sh.newsales values(908004,104,to_date('2005-12-10','yyyy-mm-dd'),'a',88004,100,600);

                        insert into sh.newsales values(908005,105,to_date('2007-11-14','yyyy-mm-dd'),'a',88005,100,500);

                        commit;

     2.3 Create and Manage encrypted tablespaces

              2.3.1 Create the encrypted tablespace in PROD3:

              2.3.2 Execute the given script (/home/oracle/scripts/sec_tbs.sql), which has created the user statement, and temporary tablespace name is temp (创建2个表，将表移动到表空间中)

              2.3.3 The two table belongs to the SH into the encryption tablespace.

 （之前的有关于加密表空间的操作的）

    3 Create and manage LOB

        3.1 Implement securefile LOB

              3.1.1. Create a tablespace,Use the following specifications:

                        File size of 150MB.

                        Initial extent size of 64MB.

                        next extent size of 64MB.

              3.1.2. Create a new table in the HR schema in the PROD1 database with the following specifications:

                        Table name TEST_SPEC

                        Column names:

                        ID NUMBER

                        DOC BLOB

                        LOB Tablespace using created before

                        Use the deduplicate option and compress high option.

              3.1.3. Load data into the TEST_SPEC,using the script name of securelob_sp.sql（PLSQL exec 即可）

        3.2 Implement BasicFiles LOB

              3.2.1 Create a new table in the HR schema in the PROD1 database with the following specifications:

              3.2.2 Table name MAGAZINE_ARTICLES

                       Tablespace USERS

                       Column names:

                       AUTHOR VARCHAR2(30)

                       ARTICLE_NAME VARCHAR2(50)

                       ARTICLE_DATE DATE

                       ARTICLE_DATA CLOB

              3.2.3 Tablespace LOB_DATA with a 16KB chunk size,initial and next extents each with a size of 2MB.

              3.2.4 Use the nocache option and disable storage in row.

              3.2.5 Use import dump to populate the HR.MAGAZINE_ARTICLES table with data from the exp_mag.dmp file.

                       (/home/oracle/scripts/exp_mag.dmp) （imp）

    4. Implement fine-grained access control (Fine-Grained Auditing)

        4.1 Set up FGA on the SALARY and COMMISSION_PCT columns of the EMPLOYEES table in the HR schema of the PROD1 database. An audit record should be created if either of these two columns are selected as part of the output of a query or are used in the where condition of a select statement and their values are not null.

        4.2 Validate that the FGA is taking place by executing statements that should result in auditing records being created and by executing statements where no auditing records will be generated. Do not delete your audit records.

    5. Administer flashback data

        5.1  Create a table named ORIGINAL_SALARY in the HR schema in the PROD1 database that includes the employee_id,commission_pct and salaries of all records in the HR.EMPLOYEES table.Note the date and time and then commit the changes.

        5.2  Delete all employees in the HR.ORIGINAL_SALARY table whose HIRE_DATE is before 2004. Commit the changes.

Create a view HR.SALARY_VIEW that will show all the original rows before the deletion.

        5.3  A hr schema table(DEPARTMENTS_NEW) was repeatedly asked DROP, return to the specified data version table include department_name column tablespace:dev_data

        5.4  Flashback archive

               5.4.1 Create a new tablespace name of FRATBS, file size of 50MB.

               5.4.2 Create Flashback Data Archive name of fra1,using the tablespace name of FRATBS,The archive administrator name of FR_ADMIN.

               5.4.3 Enable Flashback Data Archive for a table name of SH.PROMOTIONS, and includes data that will be retained for one years.

               5.4.4 Examine the contents of the table SH.PROMOTIONS that before existed 20 minutes to 1 minutes.