#眉標=Oracle、Object、ROWID
#副標=管理Oracle資料庫必要的功力(7)
#大標=運用物件讓資料庫管理增加彈性(中)
#作者=文／圖 張天慧

==============box 範例1============
CREATE OR REPLACE VIEW TEST.V_TEST_TABLE AS 
SELECT A.COLUMN1, A.COLUMN2, B.COLUMN4, B.COLUMN5
FROM TEST.TEST_TABLE01 A, TEST.TEST_TABLE02;
================end================


=============box 語法1=========
GRANT CREATE INDEX TO TEST_ACCOUNT;
=============end=========


=============box語法2=========
GRANT CREATE ANY INDEX TO TEST_ACCOUNT;
=============end=========


===========box範例2============
CREATE [ UNIQUE ] INDEX [ schema. ]INDEX_NAME ON [ schema. ] table
( column [ ASC | DESC ] [ , column [ ASC | DESC ] ] …)
[ TABLESPACE tablespace ] [ PCTFREE integer ][ INITRANS integer ]
[ MAXTRANS integer ][ storage-clause][ LOGGING | NOLOGGING ][ NOSORT ];
==================end========================


================box範例3===============
DEFAULT STORAGE ( INITIAL integer[K|M]
  NEXT integer[K|M] MINEXTENTS integer MAXEXTENTS integer
  PCTINCREASE integer BUFFER POOL[DEFAULT | KEEP| RECYCLE]
======================end==================


================box範例4===============
CREATE UNIQUE INDEX TEST.TEST_TABLE_INDX01
ON TEST.TEST_TABLE (COLUMN01, COLUMN02)
INITRANS 2 MAXTRANS 255
TABLESPACE INDX STORAGE
( INITIAL 64K NEXT 64K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 20);
======================end==================


================box 範例5===============
CREATE INDEX TEST.TEST_TABLE_INDX02
ON TEST.TEST_TABLE (COLUMN03)
INITRANS 2 MAXTRANS 255 TABLESPACE INDX STORAGE
  ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS 
   UNLIMITED PCTINCREASE 20);
======================end==================



================box 範例6===============
CREATE INDEX TEST.TEST_TABLE_INDX03
ON TEST.TEST_TABLE (COLUMN04)
INITRANS 2 MAXTRANS 255 TABLESPACE INDX STORAGE
( INITIAL 64K NEXT 64K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 20) REVERSE;
======================end==================



================box 範例7===============
CREATE INDEX TEST.TEST_TABLE_INDX04 
ON TEST.TEST_TABLE (UPPER(COLUMN05)) ;

======================end==================



================box 語法3===============
　　SELECT * FROM TEST.TEST_TABLE WHERE UPPER(COLUMN05) = 'AAA';
======================end==================



================box 範例8===============
CREATE BITMAP INDEX TEST.TEST_TABLE_INDX05
ON TEST.TEST_TABLE (COLUMN06)
INITRANS 2 MAXTRANS 255 TABLESPACE INDX STORAGE
( INITIAL 64K NEXT 64K MINEXTENTS 1MAXEXTENTS UNLIMITED);
======================end==================


================box 範例9===============
ALTER INDEX TEST.TEST_TABLE_INDX01 ALLOCATE EXTENT
（SZIE 500K DATAFILE ‘/u01/oracle/data/test/users_indx_01.dbf’）;
======================end==================


================box 範例10===============
　　ALTER INDEX TEST.TEST_TABLE_INDX01 DEALLOCATE UNUSED ;
======================end==================


================box 範例11===============
ALTER INDEX TEST.TEST_TABLE_INDX01 DEALLOCATE UNUSED KEEP 0K;
======================end==================



================box 範例12===============
　　ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD;

======================end==================



================box 範例13===============
ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD TABLESPACE INDX02;
======================end===============



================box 範例14===============
ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD REVERSE;
======================end===============


================box 範例15===============
ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD NOREVERSE;
======================end===============


================box 範例16===============
ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD
STORAGE ( INITIAL 128K NEXT 128K MAXEXTENTS 500);
======================end===============


================box 範例17===============
ALTER INDEX TEST.TEST_TABLE_INDX01 REBUILD ONLINE;
======================end===============


================box 範例18===============
ALTER INDEX TEST.TEST_TABLE_INDX01 COALESCE;
======================end===============


================box 範例19===============
　　DROP INDEX TEST.TEST_TABLE_INDX01;
======================end===============



================box 範例20===============
ANALYZE INDEX TEST.TEST_TABLE_INDX01 COMPUTE STATISTICS;
======================end===============



================box 範例21===============
ANALYZE INDEX TEST.TEST_TABLE_INDX01 ESTIMATE STATISTICS SAMPLE 20 PERCENT;
======================end===============



================box 範例22===============
　　DBMS_STATS.GATHER_INDEX_STATS(‘TEST', 'TEST_TABLE_INDX01', NULL);
======================end===============


================box 範例23===============
ANALYZE INDEX TEST.TEST_TABLE_INDX01 DELETE STATISTICS;
======================end===============



================box 範例24===============
GRANT CREATE SEQUENCE TO TEST_ACCOUNT;
======================end===============


================box 範例25===============
GRANT CREATE ANY SEQUENCE TO TEST_ACCOUNT;
======================end===============



================box 範例26===============
CREATE SEQUENCE  [ schema. ]SEQUENCE_NAME
 [INCREMENT BY integer] [START WITH integer]
 [MAXVALUE| NOMAXVALUE integer] 
[MINVALUE| NOMINVALUE integer]
 [CYCLE| NOCYCLE integer] [CACHE| NOCYCLE integer]

======================end===============


================box 範例27===============
CREATE SEQUENCE TEST.SEQUENCE_NAME
 INCREMENT BY 1  START WITH 1
 MAXVALUE 10000  MINVALUE 1  NOCYCLE  CACHE 10;
======================end===============



================box 範例28===============
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;
======================end===============
     
新增（Insert）語法如範例29。
================box 範例29===============
INSERT INTO TEST.TEST_TABLE01 VALUES
( SEQUENCE_NAME.CURRVAL, ‘AAA’,’BBB’);
======================end===============



================box 範例30===============
ALTER SEQUENCETEST. SEQUENCE_NAME
INCREMENT BY 2  MAXVALUE 10000  CYCLE  NOCACHE?;
======================end===============


================box 範例31===============
DROP SEQUENCE TEST.SEQUENCE_NAME;
======================end===============


================box 範例32===============
GRANT CREATE DATABASE LINK TO TEST_ACCOUNT;
======================end===============


================box 範例33===============
GRANT CREATE ANY DATABASE LINK TO TEST_ACCOUNT;
======================end===============


================box 範例34===============
CREATE [PUBLIC] DATABASE LINK databae_linke_name
  [CONNECT TO [ username IDENTIFIED BY password] [USING]
======================end===============


================box 範例35===============
CREATE PUBLIC DATABASE LINK db_link
  CONNECT TO test IDENTIFIED BY “password” USING test02;
======================end===============


================box 範例36===============
CREATE PUBLIC DATABASE LINK db_link
  CONNECT TO test IDENTIFIED BY “password”
  USING ‘(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL =TCP)(HOST = 127.0.0.1)(PORT=1521)))
       (CONNECT_DATA =SERVICE_NAME = test02) ))’;
======================end===============


================box 範例37===============
SELECT * FROM TEST.TEST_TABLE01@test02 WHERE COLUMN01=’AAA’;
======================end===============


================box 範例38===============
DROP PUBLIC DATABASE LINK test02;
======================end===============




　　　12