create table test (
    test_id number( 9 )
  , active_flag number( 1 )
  , constraint ch_test_active_flag
      check (active_flag is null or active_flag =  1 )
)
/
insert into test(test_id, active_flag) values ( 1 , null);
insert into test(test_id, active_flag) values ( 1 , null);
insert into test(test_id, active_flag) values ( 1 ,  1 );
insert into test(test_id, active_flag) values ( 2 , null);
insert into test(test_id, active_flag) values ( 2 ,  1 );
commit;

create index ix_test_active on test (decode(active_flag,  1 , test_id))
/

alter table test add constraint ak_test_active
  unique (decode(active_flag,  1 , test_id))
  deferrable initially deferred
  using index ix_test_active
/
ORA- 00904 : : invalid identifier


alter table test add constraint ak_test_active
  unique (active_flag)
  deferrable initially deferred
  using index ix_test_active
/

ORA- 01418 : specified index does not exist

create table test (name varchar2( 64 ))
/
create index ix_test_name on test(upper(name))
/
select table_name, column_name, data_type, data_default 
from dba_tab_cols 
where owner = user 
  and table_name like 'TEST'
/
select index_name, table_name, column_name 
from user_ind_columns 
where index_name like 'IX_TEST_NAME'
/