-- DB Optimization 

--1. REBUILD ALL INDEXES  INTO TABLESPACE = INDEXES 

--CREATE BIGFILE TABLESPACE "INDEXES" DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\GCOOP\INDEXES' SIZE 5M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD  ; ' as sql  from ALL_INDEXES where OWNER='ISCOTRN' order by INDEX_NAME asc;
begin
 for i in (select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD  ' as sql  from ALL_INDEXES where OWNER='ISCOTRN' order by INDEX_NAME asc ) LOOP
  execute immediate i."SQL"||' ';
 end loop;
end;
/


--2. REBUILD INDEX  STATUS = 'UNUSABLE';

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' order by INDEX_NAME asc ;
begin
 for i in (SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD' as sql FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE' order by INDEX_NAME asc  ) LOOP
  execute immediate i."SQL"||' ';
 end loop;
end;
/

--3.ALTER ADD INDEX
 CREATE UNIQUE INDEX "DPDEPTMASTER_MEM_INX" ON "DPDEPTMASTER" ("DEPTACCOUNT_NO","MEMBER_NO") ;