Oracle-测试创建反向索引及表分区和索引分区

-- 测试创建反向索引及表分区和索引分区，并把索引指定在不同的表空间上
-- 用户有了unlimited TABLESPACE权限，就可以在任何表空间可以创建对象
-- 创建主键上的反向索引，要注意先创建索引，在设主键
-- 可以把表分到
过程：
1. 用sys/orcl11R2 as sysdba登记PL/SQL Developer

2. 查看已有表空间信息： SELECT * FROM dba_tablespaces ; 
     SELECT tablespace_name FROM dba_tablespaces ;    -- 只查看关注的列
    TABLESPACE_NAME
    1 SYSTEM
    2 SYSAUX
    3 UNDOTBS1
    4 TEMP
    5 USERS
    6 EXAMPLE
    7 TS_LS_201701
    8 TS_YBSH_BASE

3.  查询表空间文件信息： SELECT * FROM dba_data_files ; 
  SELECT file_name,tablespace_name FROM dba_data_files ;   -- 只查看关注的列
    FILE_NAME TABLESPACE_NAME
1 D:\APP\LZHPC\ORADATA\ORCL\USERS01.DBF USERS
2 D:\APP\LZHPC\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
3 D:\APP\LZHPC\ORADATA\ORCL\SYSAUX01.DBF SYSAUX
4 D:\APP\LZHPC\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
5 D:\APP\LZHPC\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE
6 D:\APP\LZHPC\ORADATA\ORCL\TS_LSYB_201701.DBF TS_LS_201701
7 D:\APP\LZHPC\ORADATA\ORCL\TS_YBSH_BASE_1.DBF TS_YBSH_BASE
8 D:\APP\LZHPC\ORADATA\ORCL\INDEX_TS.DBF INDEX_TS
9 D:\APP\LZHPC\ORADATA\ORCL\INDEX_TABLESPACE.DBF INDEX_TABLESPACE

4. 创建测试表空间(index_ts)和索引表空间(index_tablespace)
 CREATE TABLESPACE index_ts 
 logging  
 datafile 'D:\APP\LZHPC\ORADATA\ORCL\index_ts.DBF' 
 size 50m  
 autoextend on  
 next 10m maxsize 2000m  
 extent management local; 

 CREATE TABLESPACE index_tablespace 
 logging  
 datafile 'D:\APP\LZHPC\ORADATA\ORCL\index_tablespace.DBF' 
 size 50m  
 autoextend on  
 next 10m maxsize 2000m  
 extent management local; 

5. 在次查询确认表空间文件信息： SELECT * FROM dba_data_files ; 

6. 创建用户并给用户授权
     CREATE USER index_ts IDENTIFIED BY 1     -- 创建用户并指定表空间
     DEFAULT TABLESPACE INDEX_TS
     TEMPORARY TABLESPACE TEMP ;
 
     GRANT unlimited TABLESPACE TO index_ts;  -- unlimited tablespace权限：用户在任何表空间可以创建对象
     GRANT CONNECT,RESOURCE TO index_ts;      -- 给用户连接和资源权限

7.  用index_ts用户登录

    select username,default_tablespace from user_users;  -- 查看当前用户的缺省表空间

    create table T_ORDER ( ORDER_ID NUMBER(10) not null,　CLIENT VARCHAR2(60),ADDRESS VARCHAR2(100),ORDER_DATE CHAR(8));
    -- 要保证创建IDX_ORDER_ID的SQL语句在创建PK_ORDER主键的SQL语句之前，因为主键需要引用到这个反向键索引。
    create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) REVERSE TABLESPACE index_tablespace ;
   alter table T_ORDER add constraint PK_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;

    select * from user_tables;  --查看用户下所有的表信息
    SELECT * FROM user_indexes ;  --查看用户下索引信息

8. 结果：
    -- Create table
    create table T_ORDER
    (
      order_id   NUMBER(10) not null,
      client     VARCHAR2(60),
      address    VARCHAR2(100),
      order_date CHAR(8)
    )
    tablespace INDEX_TS            -- 默认表空间 INDEX_TS
      pctfree 10
      initrans 1
      maxtrans 255;
    -- Create/Recreate indexes 
    create unique index IDX_ORDER_ID on T_ORDER (ORDER_ID)
      tablespace INDEX_TABLESPACE  -- 索引表空间 INDEX_TABLESPACE
      pctfree 10
      initrans 2
      maxtrans 255
      reverse;
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table T_ORDER
      add constraint PK_ORDER primary key (ORDER_ID);


9. 创建分区表
create table GG_AC43
(
  baz001 NUMBER(16) not null,
  baz002 NUMBER(16),
  bab221 NUMBER(20) not null,
  aac001 NUMBER(16) not null,
  aab001 NUMBER(16) not null,
  aae002 NUMBER(6) not NULL)
  partition by range (AAE002)
(
  partition P_GG_AC43_1992 values less than (199301)
    tablespace TP_ZJKJMJF
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
      partition P_GG_AC43_2029 values less than (203001)
    tablespace TP_ZJKJMJF
    pctfree 10
    initrans 1
    maxtrans 255,
  partition P_GG_AC43_2030 values less than (MAXVALUE)
    tablespace TP_ZJKJMJF
    pctfree 10
    initrans 1
    maxtrans 255
);


-- 结果：
select * from user_tables;  --查看用户下所有的表信息
SELECT table_name,tablespace_name, status,partitioned FROM user_tables ;  -- 只查看关注的列
TABLE_NAME TABLESPACE_NAME STATUS PARTITIONED
1 GG_AC43 VALID YES
2 T_ORDER INDEX_TS VALID NO


SELECT * FROM user_indexes ;  --查看用户下索引信息
SELECT index_name,index_type,table_owner,table_name,tablespace_name FROM user_indexes;  -- 只查看关注的列
    INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLESPACE_NAME
1 IDX_ORDER_ID NORMAL/REV INDEX_TS T_ORDER INDEX_TABLESPACE
2 IDX_GG_AC43_AAB001 NORMAL INDEX_TS GG_AC43 INDEX_TABLESPACE
3 IDX_GG_AC43_AAE002 NORMAL INDEX_TS GG_AC43 TS_YBSH_BASE