How to drop index and before dropping it how to get the DDL.
Oracle DROP INDEX statement
DROP INDEX [schema_name.]index_name;
DECLARE index_count INTEGER;
BEGIN
SELECT COUNT(*) INTO index_count
FROM USER_INDEXES
WHERE INDEX_NAME = 'index_name';
IF index_count > 0 THEN
EXECUTE IMMEDIATE 'DROP INDEX index_name';
END IF;
END;
/
In case want to check whether index exists and then delete it.
If try to drop a non-existing, you will get the following error:
SQL Error: ORA-01418: specified index does not existIn SQLplus, set these before running the commands
set long 100000
set longchunksize 100000
select DBMS_METADATA.GET_DDL('INDEX', 'index_name')
from all_indexes
where owner in (USER, 'USER_OTHER_THAN_LOGGED_IN_USER');
Use below script to fetch metadata other information.
SELECT DBMS_METADATA,GET_GRANTED_DDL('SYSTEM_GRANT','SSO******') from dual;
Comments
Post a Comment