 |
|
Oracle Tips by Burleson |
Punch All Index Syntax
As we may recall from tutorial 4, the
dbms_metadata package can be used to punch the index creation syntax
from the Oracle data dictionary. Your mission is to write a query
that uses dbms_metadata to punch the entire index DDL for the sample
database.
Refer to Google (search for dbms_metadata and
index), or review tutorial 4 to get the syntax. Your correct answer
should look something like this:
CREATE
INDEX "PUBS"."AUTHOR_LAST_NAME_LOWER" ON "PUBS"."AUTHOR"(LOWER("AUTHOR
_LAST_NAME"))
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE
0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"TEST_16K"
CREATE
INDEX "PUBS"."AUTH_LAST_NAME" ON "PUBS"."AUTHOR" ("AUTHOR_LAST_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE
0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"USERS"
CREATE
BITMAP INDEX "PUBS"."BITMAP_BOOK_TYPE" ON "PUBS"."BOOK" ("BOOK_TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0
FREELISTS
1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
CREATE
INDEX "PUBS"."BTREE_TITLE_TYPE" ON "PUBS"."BOOK" ("BOOK_TITLE")
PCTFREE 10 INITRANS 2 MAXTRANS
255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE
0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"USERS"
CREATE
UNIQUE INDEX "PUBS"."I_SNAP$_BOOK_TYPE_SALES" ON
"PUBS"."BOOK_TYPE_SALE
S" (SYS_OP_MAP_NONNULL("BOOK_TYPE"))
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE
0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"TEST_16K"
CREATE
UNIQUE INDEX "PUBS"."I_SNAP$_PUB_SALARIES" ON "PUBS"."PUB_SALARIES" (SYS_OP_MAP_NONNULL("PUB_NAME"),
SYS_OP_MAP_NONNULL("JOB_NAME"))
PCTFREE 10 INITRANS 2 MAXTRANS
255
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE
0
FREELISTS
1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_16K"
Click below to see the answer:
set pagesize
0
set long 90000
SELECT
DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
