Author: Dev

To add primary key on a table of Oracle database you must use the alter table command. Syntax ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(col_name); Add primary key example ALTER TABLE test ADD CONSTRAINT pk_test_id PRIMARY KEY(id); Output: table TEST altered. Check constraints SELECT OWNER, CONSTRAINT_NAME, STATUS FROM user_constraints WHERE table_name = ‘TEST’; Output:…(Continue Reading)

Drop index oracle example You can remove unnecessary indexes from oracle database using pl/sql command drop index. Syntax: DROP INDEX index_name; Example: — drop index DROP INDEX AMOUNT_IDX; Output: index AMOUNT_IDX dropped.

Alter index oracle examples Below are a list of oracle pl/sql alter index examples. You can learn how to alter indexes using commands like: rename an index, disable an index, drop index partition, rebuild index, collect statistics. Rename an Index Disable an Index Enable an Index Disable an Function-Based Index Enable an Function-Based Index Rename…(Continue Reading)

Rebuild an Index Partition example In oracle database to rebuild an index partition you must use the command alter index with REBUILD PARTITION keyword. — create Range-Partitioned Global Index CREATE INDEX amount_idx ON orders (amount) GLOBAL PARTITION BY RANGE (amount) (PARTITION part_1 VALUES LESS THAN (1000), PARTITION part_2 VALUES LESS THAN (2000), PARTITION part_3 VALUES…(Continue Reading)

Set Index Parallel example In Oracle database to set an index parallel you must use the command alter index with REBUILD PARALLEL keyword. — set Index Parallel ALTER INDEX STU_LN_IDX REBUILD PARALLEL; Output: index STU_LN_IDX altered. Check indexes select INDEX_NAME, DEGREE, INSTANCES from USER_INDEXES WHERE TABLE_NAME=’STUDENTS_LIST’; Output: INDEX_NAME DEGREE INSTANCES STUDENT_IDX 1 1 STU_LN_IDX DEFAULT…(Continue Reading)