Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the new features of Oracle12C". In daily operation, I believe many people have doubts about the new features of Oracle12C. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the new features of Oracle12C?" Next, please follow the editor to study!

1. Read-only partition or subpartition

Tables, partitions, and subpartitions can be set to read-only in the Oracle12.2 database to protect data from unintentional DML operations by any user or trigger.

The value of a read-only clause can be READ ONLY or READ WRITE. READ WRITE is the default value. Unless the read-only clause is explicitly set for the partition or subpartition, the higher-level setting of the read-only clause will be applied to the partition and subpartition!

Example: create a table with read-only and read-write partitions: orders_read_write_only

The following is an example of creating a compound range list partition table with read-only and read-write states. Orders_read_write_only is explicitly specified as READ WRITE, so the default property of the table is read and write. The default property of partition, order_p1, is specified as read-only, so the subpartitions ord_p1_northwest and order_p1_southwest inherit the read-only state order_p1 from the partition. The subpartitions ord_p2_southwest and order_p3_northwest are explicitly specified as read-only, overriding the default read-write state.

CREATE TABLE orders_read_write_only (order_id NUMBER (12), order_date DATE CONSTRAINT order_date_nn NOT NULL, state VARCHAR2 (2)) READ WRITE PARTITION BY RANGE (order_date) SUBPARTITION BY LIST (state) (PARTITION order_p1 VALUES LESS THAN (TO_DATE ('01lyDECMYY')) READ ONLY (SUBPARTITION order_p1_northwest VALUES ('OR',' WA') SUBPARTITION order_p1_southwest VALUES ('AZ',' UT', 'NM'), PARTITION order_p2 VALUES LESS THAN (SUBPARTITION order_p2_northwest VALUES (' OR', 'WA'), SUBPARTITION order_p2_southwest VALUES (' AZ', 'UT',' NM') READ ONLY), PARTITION order_p3 VALUES LESS THAN ('01MurJULML 2016') 'DD-MON-YYYY') (SUBPARTITION order_p3_northwest VALUES (' OR', 'WA') READ ONLY, SUBPARTITION order_p3_southwest VALUES (' AZ', 'UT',' NM')

-- check the status of partitions and subpartitions:

SQL > SELECT PARTITION_NAME, READ_ONLY FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'ORDERS_READ_WRITE_ONLY' PARTITION_NAME READ- ORDER_P1 YES-read-only status ORDER_P2 NONEORDER_P3 NONESQL > set linesize 300SQL > col PARTITION_NAME for a25SQL > col SUBPARTITION_NAME for a25SQL > SELECT PARTITION_NAME, SUBPARTITION_NAME, READ_ONLY FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME = 'ORDERS_READ_WRITE_ONLY' PARTITION_NAME SUBPARTITION_NAME REA- ORDER_P1 ORDER_P1_NORTHWEST YESORDER_P1 ORDER_P1_SOUTHWEST YESORDER_P2 ORDER_P2_NORTHWEST NO ORDER_P2 ORDER_P2_SOUTHWEST YESORDER_P3 ORDER_P3_NORTHWEST YESORDER_P3 ORDER_P3_SOUTHWEST NO6 rows selected.

As you can see from the above, setting the read-only state of a subpartition separately will overwrite the read-write properties of the partition, that is, setting the read-only state of a subpartition of one partition will not affect the properties of other subpartitions under this partition! )

-: the properties of the partition can be modified online through the modify statement.

SQL > alter table ORDERS_READ_WRITE_ONLY modify partition ORDER_P1 read write

#

2. Create an external table partition:

In 12.2, Oracle also supports external table partitioning, and the syntax similar to the following shows the use of this feature. For a unified external table, you can point to different external files through partitioning, and different files can be used to store classified data, thus making the use of external tables more flexible:

Example:

SQL > Create directory load_d1 as'/ home/oracle' CREATE TABLE sales (loc_id number, prod_id number, cust_id number, amount_sold number, quantity_sold number) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY load_d1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII NOBADFILE LOGFILE log_dir:'sales.log' FIELDS TERMINATED BY ",") REJECT LIMIT UNLIMITED PARTITION BY RANGE (loc_id) (PARTITION p1 VALUES LESS THAN (1000) LOCATION ('california.txt'), PARTITION p2 VALUES LESS THAN (2000) DEFAULT DIRECTORY load_d2 LOCATION (' washington.txt')) PARTITION p3 VALUES LESS THAN (3000))

# # #

3. List partition:

The semantics of creating a list partition is very similar to that of creating a range partition. Starting with Oracle12.2, you can create a multi-column list partition.

Example: create a list partition

CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2 (20), quarterly_sales number (10,2), state varchar2 (2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR',' WA'), PARTITION q1_southwest VALUES ('AZ',' UT', 'NM'), PARTITION q1_northeast VALUES (' NY', 'VM',' NJ') PARTITION q1_southeast VALUES ('FL',' GA'), PARTITION q1_northcentral VALUES ('SD',' WI'), PARTITION q1_southcentral VALUES ('OK',' TX'))

3.1. Create a list partition using the default partition: (to specify the default partition is to insert all the rows that do not match into the default partition)

Unlike range partitions, using list partitions, there is no obvious sense of order between partitions.

You can also specify a default partition to map rows that are not mapped to any other partition. If a default partition is specified in the previous example, the state CA is mapped to that partition.

Example: create a list partition table using the default partition

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR (30), state_code VARCHAR (2), sale_date DATE) STORAGE (INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) (PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 8m) TABLESPACE tbs8 PARTITION region_west VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING, PARTITION region_south VALUES (' TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES ('OH','ND','SD','MO','IL','MI','IA') PARTITION region_null VALUES (NULL), PARTITION region_unknown VALUES (DEFAULT))

3.2. Create an automatic list partition:

The automatic list partitioning method allows list partitions to be created on demand.

An automatic list partitioned table is similar to a regular list partitioned table, but it is easier to manage. You can create an automatic list partition table using only known partition key values. When data is loaded into a table, if the loaded partition key value does not correspond to any existing partition, the database automatically creates a new partition. Because partitions are created automatically on demand, the automatic list partitioning method is conceptually similar to existing interval partitioning methods.

Note:

①: automatic list partitioning for data types whose values change very frequently is not suitable for this approach unless you can adjust the data.

②: the automatic list partition table must have at least one partition when it is created. Automatic list partitions cannot have DEFAULT partitions because new and unknown partition keys are automatically created.

Example:

CREATE TABLE sales_auto_list (salesman_id NUMBER (5), salesman_name VARCHAR2 (30), sales_state VARCHAR2 (20), sales_amount NUMBER (10), sales_date DATE) PARTITION BY LIST (sales_state) AUTOMATIC (PARTITION P_CAL VALUES ('CALIFORNIA'))

-- check whether the list partition is an automatic list partition

SQL > col TABLE_NAME for a20SQL > col owner for a10SQL > set linesize 300SQL > select OWNER,TABLE_NAME,PARTITIONING_TYPE,AUTOLIST from dba_PART_TABLES where table_name=upper ('sales_auto_list'); OWNER TABLE_NAME PARTITION AUT- SYS SALES_AUTO_LIST LIST YES

3.3. Create a multi-column list partition:

Similar to single-column list partitions, each partition can contain a collection that contains a list of values.

Syntax: PARTITION BY LIST (column1,column2)

Example: create a multi-column list partition

CREATE TABLE dba_by_db_in_yhem (dbalic NUMBER, username VARCHAR2 (20), dbcat VARCHAR2 (4), region VARCHAR2 (10)) PARTITION BY LIST (dbcat, region) (PARTITION north_part VALUES (('ORCL','BEIJING'), (' ORCL','TIANJIN')), PARTITION south_part VALUES (('DB2','SHENZHEN'), (' DB2','GUANGZHOU') PARTITION west_part VALUES (('SQL','CHENGDU'), (' ORCL','CHENGDU'), ('DB2','KUNMING')), PARTITION east_part VALUES (' ORCL','SHANGHAI'), PARTITION rest VALUES (DEFAULT)) Insert into dba_by_db_in_yhem values (1); insert into dba_by_db_in_yhem values (2); insert into dba_by_db_in_yhem values (3); insert into dba_by_db_in_yhem values (4); (4) insert into dba_by_db_in_yhem values (4). Insert into dba_by_db_in_yhem values; SQL > select * from dba_by_db_in_yhem partition (north_part) DBALIC USERNAME DBCA REGION--1 EYGLE ORCL BEIJING 2 KAMUS ORCL BEIJINGSQL > select * from dba_by_db_in_yhem partition (south_part) DBALIC USERNAME DBCA REGION--4 ORA-600 DB2 GUANGZHOU

# # #

Specify the partition of the key column:

For range partitions and hash partition tables, you can specify up to 16 partition key columns.

1. Create a multi-column range partition table by date

CREATE TABLE sales_demo (year NUMBER, month NUMBER, day NUMBER, amount_sold NUMBER) PARTITION BY RANGE (year,month) (PARTITION before2001 VALUES LESS THAN (2001 VALUES LESS THAN 1), PARTITION Q1 (2001 VALUES LESS THAN), PARTITION Q2 (2001 VALUES LESS THAN (2001) 7), PARTITION Q3 (2001 VALUES LESS THAN (2001) 10), PARTITION Q4 (2001 VALUES LESS THAN (2002), PARTITION future VALUES LESS THAN (MAXVALUE,0) INSERT INTO sales_demo VALUES (2000 ~ 12, 1000); INSERT INTO sales_demo VALUES (2001 ~ 3, 17, 2000); INSERT INTO sales_demo VALUES (2001 ~ 11, 1, 5000); INSERT INTO sales_demo VALUES (2002, 1, 4000)

2. Create a multi-column range partition table (enforce equal partitions)

CREATE TABLE supplier_parts (supplier_id NUMBER, partnum NUMBER, price NUMBER) PARTITION BY RANGE (supplier_id, partnum) (PARTITION p1 VALUES LESS THAN (10100), PARTITION p2 VALUES LESS THAN (10200), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE); INSERT INTO supplier_parts VALUES (5pje 5lgr 1000); INSERT INTO supplier_parts VALUES (5pr 150p1000); INSERT INTO supplier_parts VALUES (10je 100pr 1000);-- check the values of each partition: SQL > SELECT * FROM supplier_parts PARTITION (p1) SUPPLIER_ID PARTNUM PRICE- 5 5 1000 5 150 1000SQL > SELECT * FROM supplier_parts PARTITION (p2) SUPPLIER_ID PARTNUM PRICE- 10100 1000 at this point, the study on "what are the new features of Oracle12C" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.