The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.
The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly. Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables. Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.
global indexes are made unusable when you drop or move a partition. This is because there are entries pointing to physical addresses in your partition, that are no longer valid. If you can, try to avoid global indexes. You can do this by making them local.
create index idx_fk_opt_run_case on opt_run (case_id) local;
Should do the trick.
If you can use reference partitioning, you don't have to add the redundant data in the table because the relation is already defined in the partition definition.
an example:
RONR SQL>create table parent
(id number not null
, x varchar2(10)
)
partition by list (id) (
partition par_1 values (1),
partition par_2 values (2)
); 2 3 4 5 6 7 8
Table created.
RONR SQL>create unique index pk_parent
on parent (id) local;
2
Index created.
RONR SQL>alter table parent
add CONSTRAINT pk_parent
PRIMARY KEY (id)
USING INDEX; 2 3 4
Table altered.
RONR SQL>create table child
( parent number not null
, y varchar2(10)
,
constraint fk_p_c
foreign key (parent) references parent(id))
partition by reference (fk_p_c); 2 3 4 5 6 7
Table created.
RONR SQL>insert into parent (id, x) values (1,'boe');
insert into parent (id, x) values (2,'oeps');
1 row created.
RONR SQL>
1 row created.
RONR SQL>select * from parent;
ID X
---------- ----------
1 boe
2 oeps
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (1,'ggg');
1 row created.
RONR SQL>insert into child (parent, y) values (2,'ppp');
1 row created.
RONR SQL>select * from child;
PARENT Y
---------- ----------
1 ggg
1 ggg
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_1
PARENT PAR_2
CHILD PAR_1
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_1 USABLE
PK_PARENT PAR_2 USABLE
RONR SQL>alter table parent drop partition par_1;
Table altered.
RONR SQL>select * from parent;
ID X
---------- ----------
2 oeps
RONR SQL>select * from child;
PARENT Y
---------- ----------
2 ppp
RONR SQL>select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
PARENT PAR_2
CHILD PAR_2
RONR SQL>select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PK_PARENT PAR_2 USABLE
I see no unusable index.
What I do see is that I can drop a parent partition and this cascades to the child partition.
does this help?
Best Answer
I have several poor answers. I hope one of them works for you.
Moving the packages to another schema may be an option. You might then grant that other schema a bare minimum set of privileges. This would get ugly for name resolution, though.
Auditing the code by searching the schema might be another option:
Defining the package as AUTHID CURRENT_USER would scale back the permissions at runtime to the calling user's permissions. This solution has its own can of worms.