對物件的影響程度
Rebuild Table (蟾蜍) > Alter Table Move (reorg) > Repair Chained Rows (蟾蜍)
1. Rebuild Table (蟾蜍) : Create Indexes, Compile Dependencies, Analyze Table
2. Alter Table Move (reorg): Rebuild Indexes, Analyze Table
3. Repair Chained Rows (蟾蜍): Analyze Table
SQL> create table tbl_1224 as select * from employees;
Table created.
SQL> desc tbl_1224;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> CREATE INDEX idx_tbl_1224_01 ON tbl_1224
(EMPLOYEE_ID); 2
Index created.
SQL> select EMAIL from tbl_1224;
-------------------------
DOCONNEL
DGRANT
JWHALEN
MHARTSTE
PFAY
SMAVRIS
HBAER
SHIGGINS
WGIETZ
SKING
NKOCHHAR
SQL> CREATE OR REPLACE FORCE VIEW vw_1224
AS
select EMAIL from tbl_1224; 2 3
View created.
SQL> select * from vw_1224;
-------------------------
DOCONNEL
DGRANT
JWHALEN
MHARTSTE
PFAY
SMAVRIS
HBAER
SHIGGINS
WGIETZ
SKING
NKOCHHAR
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and object_type ='VIEW'; 2 3
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------- -------
HR1
VW_1224
VIEW VALID
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and status <> 'VALID';
2 3
no rows selected
--Alter Table Move 不會讓 depenedncies 的狀態成為 INVALID
SQL> alter table tbl_1224 move;
Table altered.
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and status <> 'VALID'; 2 3
no rows selected
SQL> select
a.owner OWNER,
a.INDEX_NAME NAME,
a.TABLE_NAME TABLE_NAME,
a.STATUS STATUS
from all_indexes a
where A.OWNER = 'HR1'
and A.STATUS <> 'VALID'; 2 3 4 5 6 7 8
OWNER NAME
------------------------------ ------------------------------
TABLE_NAME STATUS
------------------------------ --------
HR1 IDX_TBL_1224_01
TBL_1224 UNUSABLE
SQL> select * from vw_1224;
-------------------------
DOCONNEL
DGRANT
JWHALEN
MHARTSTE
PFAY
SMAVRIS
HBAER
SHIGGINS
WGIETZ
SKING
NKOCHHAR
SQL> ALTER INDEX IDX_TBL_1224_01 REBUILD;
Index altered.
-- TOAD Rebuild Table 會讓 depenedncies 的狀態成為 INVALID
SQL> SET LINESIZE 200
SQL> ALTER TABLE HR1.TBL_1224 RENAME TO TBL_1224_X;
Table altered.
SQL> CREATE TABLE HR1.TBL_1224
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) NOT NULL,
EMAIL VARCHAR2(25 BYTE) NOT NULL,
PHONE_NUMBER VARCHAR2(20 BYTE),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR2(10 BYTE) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
) 2 3 4 5 6 7 8 9 10 11 12 13 14
15 ;
Table created.
SQL> INSERT /*+ APPEND */
2 INTO HR1.TBL_1224 INS_TBL
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,
3 4 5 COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
SELECT
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,
COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID
FROM HR1.TBL_1224_X SEL_TBL;
6 7 8 9 10 COMMIT;
214 rows created.
SQL>
Commit complete.
SQL>
SQL>
SQL>
SQL> DROP INDEX HR1.IDX_TBL_1224_01;
Index dropped.
SQL> CREATE INDEX HR1.IDX_TBL_1224_01 ON HR1.TBL_1224
(EMPLOYEE_ID) 2 ;
Index created.
SQL> select
a.owner OWNER,
a.INDEX_NAME NAME,
a.TABLE_NAME TABLE_NAME,
a.STATUS STATUS
from all_indexes a
where A.OWNER = 'HR1'
and A.STATUS <> 'VALID'; 2 3 4 5 6 7 8
no rows selected
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and status <> 'VALID'; 2 3
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
HR1 VW_1224 VIEW INVALID
SQL> select * from vw_1224;
-------------------------
DOCONNEL
DGRANT
JWHALEN
MHARTSTE
PFAY
SMAVRIS
HBAER
SHIGGINS
WGIETZ
SKING
NKOCHHAR
-- 使用到 VIEW VW_1224 之後，則DB會自動COMPILE該物件
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and status <> 'VALID'; 2 3
no rows selected
-- 這是手動COMPILE該物件
SQL> ALTER VIEW "HR1"."VW_1224" COMPILE;
View altered.
SQL> select OWNER, object_name, OBJECT_TYPE , status
from dba_objects
where owner = 'HR1' and status <> 'VALID'; 2 3
no rows selected
SQL> select
a.owner OWNER,
a.INDEX_NAME NAME,
a.TABLE_NAME TABLE_NAME,
a.STATUS STATUS
from all_indexes a
where A.OWNER = 'HR1'
and A.STATUS <> 'VALID'; 2 3 4 5 6 7 8
no rows selected
SQL>
