oracle基礎練習11－15章 索引維護、資料庫完整性等
/* 2008/06/1 5 星期日
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習11－15章
＊索引維護、資料庫完整性（五種約束)、使用者資源和安全管理、許可權管理、角色管理
*/
SQL> select index_name,table_name,tablespace_name,index_type,uniquess,status
2 from dba_indexes
3 where wner='SCOTT';
select index_name,table_name,tablespace_name,index_type,uniquess,status
*
ERROR at line 1:
ORA-00904: "UNIQUESS": invalid identifier
SQL> desc dba_indexes;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
SQL> l
1 select index_name,table_name,tablespace_name,index_type,uniquess,status
2 from dba_indexes
3* where wner='SCOTT'
SQL> 1
1* select index_name,table_name,tablespace_name,index_type,uniquess,status
SQL> c /uniquess/uniqueness
1* select index_name,table_name,tablespace_name,index_type,uniqueness,status
SQL> l
1 select index_name,table_name,tablespace_name,index_type,uniqueness,status
2 from dba_indexes
3* where wner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_EMP EMP
PIONEER_DATA NORMAL UNIQUE VALID
EMP_NAME_IDX EMP
PIONEER_DATA NORMAL NONUNIQUE VALID
EMP_JOB_IDX EMP
PIONEER_DATA BITMAP NONUNIQUE VALID
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_DEPT DEPT
USERS NORMAL UNIQUE VALID
SQL> select index_name,table_name,column_name,index_owner,table_owner
2 from dba_ind_columns where table_owner='SCOTT';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT
DEPTNO
SCOTT SCOTT
PK_EMP EMP
EMPNO
SCOTT SCOTT
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_NAME_IDX EMP
ENAME
SCOTT SCOTT
EMP_JOB_IDX EMP
JOB
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
SCOTT SCOTT
SQL> col column_name for a10
SQL> l
1 select index_name,table_name,column_name,index_owner,table_owner
2* from dba_ind_columns where table_owner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_NAME_IDX EMP ENAME
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_JOB_IDX EMP JOB
SCOTT SCOTT
SQL> col index_name for a10
SQL> l
1 select index_name,table_name,column_name,index_owner,table_owner
2* from dba_ind_columns where table_owner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_NAME_I EMP ENAME
DX
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_JOB_ID EMP JOB
X
SCOTT SCOTT
SQL> drop index emp_name_idx;
drop index emp_name_idx
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> drop index scott.emp_name_idx;
Index dropped.
SQL> select index_name,table_name,column_name,index_owner,table_owner
2 from dba_ind_columns where table_owner='SCOTT';
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_JOB_ID EMP JOB
X
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
SQL> drop index scott.emp_job_idx;
Index dropped.
SQL> create index scott.emp_name_idx
2 on scott.emp(ename)
3 pctfree 20
4 storage(initial 100k next 100k
5 pctincrease 0 maxextents 100)
6 tablespace pioneer_indx;
Index created.
SQL> create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6 tablespace pioneer_indx);
tablespace pioneer_indx)
*
ERROR at line 6:
ORA-02158: invalid CREATE INDEX option
SQL> l
1 create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6* tablespace pioneer_indx)
SQL> c /)/
6* tablespace pioneer_indx
SQL> l
1 create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6* tablespace pioneer_indx
SQL> /
Index created.
SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status
2 from dba_indexes
3 where wner='SCOTT';
INDEX_NAME TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
INDEX_TYPE UNIQUENES STATUS
--------------------------- --------- --------
PK_EMP EMP PIONEER_DATA
NORMAL UNIQUE VALID
EMP_NAME_I EMP PIONEER_INDX
DX
NORMAL NONUNIQUE VALID
EMP_JOB_ID EMP PIONEER_INDX
X
INDEX_NAME TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
INDEX_TYPE UNIQUENES STATUS
--------------------------- --------- --------
BITMAP NONUNIQUE VALID
PK_DEPT DEPT USERS  
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習11－15章
＊索引維護、資料庫完整性（五種約束)、使用者資源和安全管理、許可權管理、角色管理
*/
SQL> select index_name,table_name,tablespace_name,index_type,uniquess,status
2 from dba_indexes
3 where wner='SCOTT';
select index_name,table_name,tablespace_name,index_type,uniquess,status
*
ERROR at line 1:
ORA-00904: "UNIQUESS": invalid identifier
SQL> desc dba_indexes;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
TABLESPACE_NAME VARCHAR2(30)
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
PCT_THRESHOLD NUMBER
INCLUDE_COLUMN NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
PCT_FREE NUMBER
LOGGING VARCHAR2(3)
BLEVEL NUMBER
LEAF_BLOCKS NUMBER
DISTINCT_KEYS NUMBER
AVG_LEAF_BLOCKS_PER_KEY NUMBER
AVG_DATA_BLOCKS_PER_KEY NUMBER
CLUSTERING_FACTOR NUMBER
STATUS VARCHAR2(8)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)
SQL> l
1 select index_name,table_name,tablespace_name,index_type,uniquess,status
2 from dba_indexes
3* where wner='SCOTT'
SQL> 1
1* select index_name,table_name,tablespace_name,index_type,uniquess,status
SQL> c /uniquess/uniqueness
1* select index_name,table_name,tablespace_name,index_type,uniqueness,status
SQL> l
1 select index_name,table_name,tablespace_name,index_type,uniqueness,status
2 from dba_indexes
3* where wner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_EMP EMP
PIONEER_DATA NORMAL UNIQUE VALID
EMP_NAME_IDX EMP
PIONEER_DATA NORMAL NONUNIQUE VALID
EMP_JOB_IDX EMP
PIONEER_DATA BITMAP NONUNIQUE VALID
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
PK_DEPT DEPT
USERS NORMAL UNIQUE VALID
SQL> select index_name,table_name,column_name,index_owner,table_owner
2 from dba_ind_columns where table_owner='SCOTT';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT
DEPTNO
SCOTT SCOTT
PK_EMP EMP
EMPNO
SCOTT SCOTT
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_NAME_IDX EMP
ENAME
SCOTT SCOTT
EMP_JOB_IDX EMP
JOB
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
SCOTT SCOTT
SQL> col column_name for a10
SQL> l
1 select index_name,table_name,column_name,index_owner,table_owner
2* from dba_ind_columns where table_owner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_NAME_IDX EMP ENAME
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_JOB_IDX EMP JOB
SCOTT SCOTT
SQL> col index_name for a10
SQL> l
1 select index_name,table_name,column_name,index_owner,table_owner
2* from dba_ind_columns where table_owner='SCOTT'
SQL> /
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_NAME_I EMP ENAME
DX
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
EMP_JOB_ID EMP JOB
X
SCOTT SCOTT
SQL> drop index emp_name_idx;
drop index emp_name_idx
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> drop index scott.emp_name_idx;
Index dropped.
SQL> select index_name,table_name,column_name,index_owner,table_owner
2 from dba_ind_columns where table_owner='SCOTT';
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
PK_DEPT DEPT DEPTNO
SCOTT SCOTT
PK_EMP EMP EMPNO
SCOTT SCOTT
EMP_JOB_ID EMP JOB
X
SCOTT SCOTT
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ------------------------------ ----------
INDEX_OWNER TABLE_OWNER
------------------------------ ------------------------------
SQL> drop index scott.emp_job_idx;
Index dropped.
SQL> create index scott.emp_name_idx
2 on scott.emp(ename)
3 pctfree 20
4 storage(initial 100k next 100k
5 pctincrease 0 maxextents 100)
6 tablespace pioneer_indx;
Index created.
SQL> create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6 tablespace pioneer_indx);
tablespace pioneer_indx)
*
ERROR at line 6:
ORA-02158: invalid CREATE INDEX option
SQL> l
1 create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6* tablespace pioneer_indx)
SQL> c /)/
6* tablespace pioneer_indx
SQL> l
1 create bitmap index scott.emp_job_idx
2 on scott.emp(job)
3 pctfree 20
4 storage (initial 100k next 100k
5 pctincrease 0 maxextents 100)
6* tablespace pioneer_indx
SQL> /
Index created.
SQL> select index_name,table_name,tablespace_name,index_type,uniqueness,status
2 from dba_indexes
3 where wner='SCOTT';
INDEX_NAME TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
INDEX_TYPE UNIQUENES STATUS
--------------------------- --------- --------
PK_EMP EMP PIONEER_DATA
NORMAL UNIQUE VALID
EMP_NAME_I EMP PIONEER_INDX
DX
NORMAL NONUNIQUE VALID
EMP_JOB_ID EMP PIONEER_INDX
X
INDEX_NAME TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
INDEX_TYPE UNIQUENES STATUS
--------------------------- --------- --------
BITMAP NONUNIQUE VALID
PK_DEPT DEPT USERS  
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12778571/viewspace-348205/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫中索引的維護 ztOracle資料庫索引
- Oracle資料庫中索引的維護(zt)Oracle資料庫索引
- Oracle資料庫中索引的維護(轉)Oracle資料庫索引
- Oracle資料庫中索引的維護 (轉帖)Oracle資料庫索引
- oracle基礎練習1－4章Oracle
- oracle基礎練習6章 表空間和資料檔案Oracle
- oracle基礎練習5章 重做日誌Oracle
- 資料維護和基礎架構維護-有感架構
- Oracle資料庫日常維護Oracle資料庫
- 資料庫第五章資料庫完整性資料庫
- Oracle 索引的維護Oracle索引
- oracle資料庫建立、刪除索引等操作Oracle資料庫索引
- ORACLE資料庫管理維護綱要Oracle資料庫
- PostgreSQL學習手冊(資料庫維護)SQL資料庫
- Oracle資料庫維護的重要性Oracle資料庫
- 【轉】Oracle資料庫日常維護手冊Oracle資料庫
- Oracle資料庫日常維護手冊 (zt)Oracle資料庫
- 維護資料庫安全資料庫
- ORACLE資料庫日常維護知識總結Oracle資料庫
- Oracle資料庫維護常用SQL語句集合Oracle資料庫SQL
- 資料庫練習資料庫
- Oracle 基礎--索引Oracle索引
- 資料庫常用維護命令資料庫
- SQL Server調優系列進階篇（如何維護資料庫索引）SQLServer資料庫索引
- ORACLE 12C PDB 維護基礎介紹Oracle
- 資料庫 資料庫的完整性資料庫
- 資料庫完整性資料庫
- oracle資料庫最佳化基礎Oracle資料庫
- SAP SD基礎知識之維護中央信貸主資料
- SAP SD基礎知識之信用範圍資料維護
- Oracle資料庫開發——表（資料完整性約束）Oracle資料庫
- 六、資料庫管理與維護資料庫
- MySQL基礎練習MySql
- JavaScript基礎練習JavaScript
- expdp基礎練習
- ORACLE資料庫日常維護的九大知識點Oracle資料庫
- Oracle資料庫維護常用的SQL程式碼示例(zt)Oracle資料庫SQL
- oracle基礎練習7－10章 儲存結構、回滾段、管理表Oracle