Toad World® Forums

Extract ddl using DBA Views, without privs to them


#1

Hi!

When user has not privs granted to use dba_views, and execute extract ddl, there is Oracle error Table or view does not exists. I think it should be message done like these in db_nav tree, means message should inform that user has no dictionary view priviledge.

Regards Piter


#2

Hi Peter,

I’m not seeing this error…
Are you getting it all the time, if the user does not have DBA views turned on?

Jaime


#3

I forgot, Jaime, it happends for partitioned/bpartitioned tables.Try this:
1 creat usere like this:

CREATE USER test1
IDENTIFIED BY test1
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
/
GRANT CREATE MATERIALIZED VIEW TO test1
/
GRANT CREATE PROCEDURE TO test1
/
GRANT CREATE SESSION TO test1
/
GRANT CREATE TABLE TO test1
/
GRANT CREATE VIEW TO test1
/
GRANT UNLIMITED TABLESPACE TO test1
/
2. connect using this user and than create table:
CREATE TABLE test_subpart
(date_d DATE,
moderators VARCHAR2(30))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (DATE_D)
SUBPARTITION BY LIST (MODERATORS)
(
PARTITION p_test VALUES LESS THAN (TO_DATE(’ 2005-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
BUFFER_POOL DEFAULT
)
NOLOGGING
(
SUBPARTITION P_TEST_SBR VALUES(‘BRUCE DUONG’) TABLESPACE users
)
)
/

3 . Enable dba dictionary views in preferences, refresh dv_nav tree and extract ddl this table…

Regards Piter


#4

Thanks for the extra info… I’ll take another look!!

Jaime


#5

Hi Piter,
I’ve still had no luck replicating your issue.
Even using your scripts and following your steps, I have no errors when I extract the ddl with the dba views turned off (in the preferences window).

We have, however, found another issue related to extracting ddl. while trying to track down your one. Under certain conditions, the sql editor is blank (with a bizarre error displayed in the output), after extracting ddl for a partitioned table.

Jaime


#6

I will check out this issue again and come back with scripts.
For this second issue, a saw that but i didn’t know when it happneds, sometimes it works ok sometimes shows clear window.


#7

It might be related to very quick navigation, and using shortcuts to activate the options (to speed up the process). But I’m not totally convinced of this yet

Jaime


#8

Ok! Try this:
create user Jaime identified by jaime;
grant create session,create any table, unlimited tablespace to jaime;

Than connect as JAIME ;).

CREATE TABLE test_subpart
(date_d DATE,
moderators VARCHAR2(30))

PARTITION BY RANGE (DATE_D)
SUBPARTITION BY LIST (MODERATORS)
(
PARTITION p_test VALUES LESS THAN (TO_DATE(’ 2005-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
NOLOGGING
(
SUBPARTITION P_TEST_SBR VALUES(‘BRUCE DUONG’) TABLESPACE users
)
);

Next check that enable using dba … is unchecked.
And extract DDL.
Table or view does not exist is showed in output, ddl window is showed bud cleared.

One more thing i’ve observed, but i don’t know that its not only on my sqlnav:
Afther this test, connect as dba user and execute:
revoke create session from jaime;

and then connect as Jaime.

My sqlnav shows error : Cannot open file D:!!\ORA-01045.HTML
D:!! is my default directory for sqlnav.

Regards


#9

Piter you’re going to hate me!! But I still don’t get the ‘table or view does not exist’ error when the DBA views are turned off
Could this be environment related? I’m using 10g2 client and 10g2 database (not on local machine).

I have been able to confirm the error after revoking the privileges!! this is not just you. I’ll log a CR for this one.

Jaime


#10

Hi Jaime,
I will do more test and get to you later, maybe i left some importand things to reproduce this. So i will check on ther database using different user.

Regards Piter

Jaime working with you is a pleasure!


#11

Thank you Piter!!
Working with you is a similar pleasure!!!


#12

Hi Jaime!
I executed all seteps like in my second scenario and i had mistake.
There should be: Next check that enable using dba … is checked
instead of:
Next check that enable using dba … is unchecked.

An then error occurs.

Regards Piter


#13

I tried both scenarios the last time I tried to replicate your issue Piter.
But I reran the scenario as you explained it again, and I still don’t get any errors extracting the DDL.
I even tried some other workflows, like creating the table without DBA views and then extracting the DDL with and without the views. Still no errors.

There must be something else that is different for you. But what could it be??

Sorry mate.

Jaime


#14

Hi Jaime!
In attachement are my steps to reproduce this issue:

  1. User is created and has grants as in script in one of previous post., you can see session privs and table in picture 1.
  2. Picture 2 has epanded subpartition node …
  3. Then I click extract ddl , in picture 3 you can see ddl settings
  4. Picture 4 screenshot afther ddl, error. Output shows error message, ddl editor is empty.

I traced sqlnav and it looks like sqlnav raise this error on this select:
22-sie-2006 23:14:00: SQL: SELECT INITIAL_EXTENT, MIN_EXTENTS,PCT_INCREASE, NEXT_EXTENT, MAX_EXTENTS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = ‘USERS’

22-sie-2006 23:14:00: [Describe Error(0)]: ORA-00942: table or view does not exist

My configuration: Database 10.2.0.2 on WinXP, client 10.2.0.2.

I have some free days (Olivier), thats i cant check this on other configurations. Of course when i come back to work i will check…

Regards Piter
foto_story.zip (314 KB)


#15

You take a well-earned break with Olivier…
SQL Nav will still be here when you get back!!

Jaime


#16

Jaime I must take brake from Olivier! Uff