延迟段创建的学习-实验

SQL> CREATE TABLE part_time_employees (
  2      empno NUMBER(8),
  3      name VARCHAR2(30),
  4      hourly_rate NUMBER (7,2)
  5      )  
  6      SEGMENT CREATION DEFERRED;

Table created.

SQL> CREATE TABLE hourly_employees (
  2      empno NUMBER(8),
  3      name VARCHAR2(30),
  4      hourly_rate NUMBER (7,2)
  5      )
  6     SEGMENT CREATION IMMEDIATE
  7     PARTITION BY RANGE(empno)
  8      (PARTITION empno_to_100 VALUES LESS THAN (100),
  9      PARTITION empno_to_200 VALUES LESS THAN (200));

Table created.

sql> select segment_name,partition_name from user_segments
rows will be truncated

SQL> col segment_name format a30
SQL> /

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
DIGITS
HOURLY_EMPLOYEES               EMPNO_TO_100
HOURLY_EMPLOYEES               EMPNO_TO_200
REG_ID_PK
COUNTRY_C_ID_PK

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
LOC_ID_PK
LOC_CITY_IX
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

28 rows selected.

SQL>  select segment_name,partition_name from user_segments where segment_name like 'HOURLY%';

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
HOURLY_EMPLOYEES               EMPNO_TO_100
HOURLY_EMPLOYEES               EMPNO_TO_200

SQL>  select segment_name,partition_name from user_segments where segment_name like 'PART%';

no rows selected

SQL>
SQL>
SQL>
SQL> SELECT TABLE_NAME, SEGMENT_CREATED FROM USER_TABLES;

TABLE_NAME       SEG
---------------- ---
LOCATIONS        YES
PART_TIME_EMPLOY NO
HOURLY_EMPLOYEES N/A
COUNTRIES        YES
ADMIN_WORK_AREA  YES
EMPLOYEES        YES
REGIONS          YES
DEPARTMENTS      YES
JOB_HISTORY      YES
JOBS             YES
DIGITS           YES

11 rows selected.

SQL> SELECT table_name, segment_created, partition_name
  2   FROM user_tab_partitions;

TABLE_NAME       SEG PARTITION_NAME
---------------- --- ------------------------------
HOURLY_EMPLOYEES YES EMPNO_TO_200
HOURLY_EMPLOYEES YES EMPNO_TO_100

SQL> INSERT INTO hourly_employees VALUES (99, 'FRose', 20.00);
INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);
1 row created.

SQL> INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);
INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00)
                                                         *
ERROR at line 1:
ORA-00911: invalid character

SQL> INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);

1 row created.

SQL> INSERT INTO hourly_employees VALUES (150, 'LRose', 25.00);

1 row created.

SQL> select count(*) from hourly_empaloyees;
select count(*) from hourly_empaloyees
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> c/hourly_empaloyees/hourly_employees;
  1* select count(*) from hourly_employees
SQL> r
  1* select count(*) from hourly_employees

  COUNT(*)
----------
         3

SQL>
SQL> select * from hourly_employees;

     EMPNO NAME                           HOURLY_RATE
---------- ------------------------------ -----------
        99 FRose                                   20
       150 LRose                                   25
       150 LRose                                   25

SQL>
SQL>
SQL> INSERT INTO part_time_employees VALUES (50, 'KReilly', 10.00);

1 row created.

SQL> INSERT INTO part_time_employees VALUES (50, 'KReilly', 10.00);

1 row created.

SQL> INSERT INTO part_time_employees VALUES (50, 'KReilly', 10.00);

1 row created.

SQL> SELECT segment_name, partition_name FROM user_segments;

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
DIGITS
PART_TIME_EMPLOYEES
HOURLY_EMPLOYEES               EMPNO_TO_100
HOURLY_EMPLOYEES               EMPNO_TO_200
REG_ID_PK

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
COUNTRY_C_ID_PK
LOC_ID_PK
LOC_CITY_IX
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX

SEGMENT_NAME                   PARTITION_NAME
------------------------------ ------------------------------
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX

29 rows selected.

SQL>