Tom,

Wouldn't you want to be very protective of such a procedure and make it only drop "the table" and not potentially drop any table? Of course, I don't know what logic you're sticking in, I just fear the openness of the procedure as you could plug any name in there. I do something similar for truncating tables currently, and I did allow a parameter in your fashion, but checked that the p_table_name was one of the three values I approved of. Is that what your logic would be, or would you also have some user checks (I'm relying on the EXECUTE privilege...should I not (nobody except other DBAs have EXECUTE ANY))?

Thanks Tom.

Dennis

Yeah, you were covered. I worded my question incorrectly. We have two different sets of procedures in place. One, where you call the procedure and it truncates a specific table (there are something like five of these). Another, where you call a procedure with a value to truncate one of three tables. I didn't want to make the developers call 100 billion procedures to truncate (well ok, three) ;) so I chose to pass in a parameter, but put in the logic to make sure it was one of the three tables.

Now, is that wrong? Is the other way better (one proc per table)? Or should I have logic to check that the user is a certain user and not worry about checking the tables (hoping the user knows what he/she/it is doing), or do the table check as an extra precaution (yes, I see big red abandon ship, missile(s) incoming warning lights all over the not including it option, but I gotta ask)?

Does checking for user 'X' do what I think it does and just protect me from those who have the execute any privilege that I don't know about and don't particularly want truncating (or dropping or whatever) my tables?
Leaving me at the point where only certain people can run my procedure, but I'm still not safe from anyone like you who can rewrite my stuff at will (hey...maybe you can also make it really fast while you're at it...).

I almost posted a reply right away, but hoped that your crystal ball was back from the shop ;) Guess I should've known better, but I was also trying to be lazy :) As well as horde time so I could think of a better way to ask it.

Thanks Tom.
Dennis

Hi

"you can grant CREATE INDEX ON TABLENAME to ANOTHER_USER to allow another schema to index some tables in your schema."

Will this index be considered by the optimizer if the owner of the table fires any query on this table that affects the indexed column (created by another user)?

Yes the index would. You can thank Tom for my example skills, I'm trying "to be like Mike".

06:48:48 WINKID1@HIS2:scsp05> create table test_me as select * from dba_objects;

Table created.

Elapsed: 00:00:00.51
06:49:00 WINKID1@HIS2:scsp05> insert into test_me select * from test_me;

8597 rows created.

Elapsed: 00:00:00.40
06:49:15 WINKID1@HIS2:scsp05> /

17194 rows created.

Elapsed: 00:00:00.10
06:49:16 WINKID1@HIS2:scsp05> /

34388 rows created.

Elapsed: 00:00:01.02
06:49:18 WINKID1@HIS2:scsp05> /

68776 rows created.

Elapsed: 00:00:02.04
06:49:21 WINKID1@HIS2:scsp05> /

137552 rows created.

Elapsed: 00:00:06.70
06:49:29 WINKID1@HIS2:scsp05> commit;

Commit complete.

Elapsed: 00:00:00.20
06:49:30 WINKID1@HIS2:scsp05> create index tab_owner_idx01 on test_me(owner);

Index created.

Elapsed: 00:00:08.92
06:50:03 WINKID1@HIS2:scsp05> @connect
userid:dbaoper
password:*********
sid:his2
Connected.
06:50:12 DBAOPER@HIS2:scsp05> create index other_owner_idx01
06:50:31 2 on winkid1.test_me(object_name, object_type);

Index created.

Elapsed: 00:00:08.03
06:50:53 DBAOPER@HIS2:scsp05> @connect
userid:winkid1
password:********
sid:his2
Connected.
06:51:00 WINKID1@HIS2:scsp05> set autotrace traceonly explain
06:51:07 WINKID1@HIS2:scsp05> select object_name, object_type
06:51:18 2 from test_me
06:51:24 3 where object_name like 'S%' and
06:51:38 4 object_type = 'TABLE'
06:51:48 5 /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'OTHER_OWNER_IDX01' (NON-UNIQUE)



06:51:49 WINKID1@HIS2:scsp05> spool off
06:51:57 WINKID1@HIS2:scsp05>

Hi

I am using oracle 8.1.7 and I was trying to grant privilege to other user so that he can create index on my tables ( not all but some of them). I tried to user CREATE INDEX ON HR_TAB to USER_01 but it failed with following error
ERROR at line 1:
ORA-00990: missing or invalid privilege

Can you please explain how to use this?

ops$tkyte@ORA817DEV> grant index on t to scott;

Grant succeeded.

 

Hi Tom,

I dropped a table. The table contains no data. I have no backups.

1)Which data dictionary table contains information about the dropped tables. ie information like the table structure, indexes associated with it, primary key , foriegn key etc ?
Or does such information exists in the datadictionary at all?

2) Is there any other way you can find those informations
about a recently dropped table (other than from the backups)?

Thanks and regards
Praveen KV

I was reading the document for init.ora parameter DML_LOCKS.

<quote>
If the value is set to 0, enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE.
</quote>

Can we use this as a protection, so that no user will be able to drop table? OR we'll face problems if we do so?