oracle基础练习11－15章索引维护、数据库完整性等

最新推荐文章于 2022-03-09 17:53:49 发布

css48419

最新推荐文章于 2022-03-09 17:53:49 发布

阅读量80

点赞数

文章标签：数据库

/* 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                      &nbsp

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-348205/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12778571/viewspace-348205/