Indexes And Constraints On Tables

 Indexes

create index &index_name on &table_name (&column_name);

ALTER INDEX &current_index_name RENAME TO &new_index_name;

CREATE INDEX &functional_index_name ON &table_name (LOWER(&column_name));

alter index &index_name unusable;

alter index &index_name rebuild;

alter index &index_name rebuild online;

drop index &index_name ;

views for indexes:

dba_indexes, user_indexes, all_indexes

select * from dba_segments where sement_name='&table_name';

Constraints 

create, add, drop, enable and disable table constraints 

create table &table_name (&column_name &datatype, &column_name &datatype, constraint &con_name unique (&column_name));

example:

CREATE TABLE employees (
    employee_id   NUMBER(5),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    email         VARCHAR2(100),
    hire_date     DATE,
    salary        NUMBER(10,2),
    department_id NUMBER(3),
    
    CONSTRAINT pk_employees PRIMARY KEY (employee_id),
    CONSTRAINT uk_email UNIQUE (email),
    CONSTRAINT fk_department FOREIGN KEY (department_id)
        REFERENCES departments (department_id),
    CONSTRAINT ck_salary CHECK (salary > 15000)
);

ADD CONSTRAINTS TO TABLE

alter table &table_name 
add 
constraint &constraint_name unique (&column_name);

DROP CONSTRAINTS FROM TABLE

alter table &table_name
drop
constraint &constraint_name;

DISABLE CONSTRAINTS ON A TABLE

alter table &table_name 
disable
constraint &constraint_name;

ENABLE CONSTRAINTS ON A TABLE

alter table &table_name
enable
constraint &constraint_name;

TO GET DETAILS/STATUS OF A CONSTRAINT :

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
all_constraints where owner=upper('&schema_name');

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
dba_constraints where owner=upper('&schema_name');

if you've logged as desired user then :

select owner, constraint_name, constraint_type, table_name, status, index_owner, index_name from
user_constraints;