延迟段创建的学习-实验
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
