Administer Tablespaces not accessible for Oracle 8i databases, bug?

Toad for Oracle
1

I have noticed in the version of Toad 10.6.1.3
The Tablespaces admin area is not accessible when connected to an Oracle database version 8i.

I get a message saying
SELECT privileges on the following are required:
DBA_FREE_SPACE,DBA_DATA_FILES,DBA_TABLESPACES,V_$TEMP_SPACE_HEADER,V_$TEMP_EXTENT_POOL,DBA_TEMP_FILES
This was also an issue in toad version 10.5.1,

but was ok in Version 10.1

is there a resolution for this issue.

Thanks

2

I just tried this with Toad 10.6 against Oracle 8.1.7 and it worked fine for me.
In Toad, click Database -> Spool SQL -> Spool to Screen. Then try to open
the window. It will capture the failing sql statement and let you know what the
error is. Post that here and I’ll advise.

3

Two things come to mind – does user have oracle privs such as select any
dictionary or select catalog role and does user have option in toad checked to
check for dba views. I think it’s just an issue of the first one, but the
second popped into my mind as well ……

4

“check for access to dba views” is a good thought, but it
doesn’t apply here. That’s more for trying to decide between dba_
and all_. When there is no all_ version of a view (dba_tablespaces has no all_
counterpart), we usually just try it and give that message if there is some
error. In this case, I’m guessing the error is not 942 but something else.
Some of the older areas of the product (like this one) will throw that message
regardless of the actual error code. Nowadays I only give messages like that if
the error code is 942. Otherwise I’ll let Oracle give the error code.

Anyway – Lushman – please post the spool sql output.

5

Thanks for the prompt replies however I have tried your suggestions but to no avail.

I am connected as the Sys user and have also tried it as the System user.

I have also tried this on a colleagues computer (running 10.5 version of toad) he also receives the same error.

I have just installed version 10.1.1.8 onto my computer and when I go to Database/Administer/Tablespaces it opens successfully showing the details I require ( connected as System or sys works)

My current work around is to have both versions installed on my computer.

I have also enclosed the message that appears when attempting to access the tablespace info in version 10.6. This looks as though it is related to permissions but it is not a problem in 10.1.1.8

Thanks

Lushman
toad error.doc (31.5 KB)

6

Thank you for the attachment, but that is not quite what I meant.

In Toad do this:

Click Database -> Spool SQL -> Spool SQL To Screen.

Click Database -> Administer -> Tablespaces. Click OK to the error.

Now, look at the bottom of Toad for the place where it says “Spool
SQL”. Do a copy/paste of all that text in there, and post it here. Then we
will be able to tell you the exact problem. It should look something like this
(only yours will have errors)

7

You all should read my recent blog on toad world about toad users over using
SYS. SYS is not for routine use – in fact I’ve never connected to
toad as SYS – not even as SYSDBA connection type. Most toad work can be
done without these ……

8

Thanks Jdorlan,

I have attached the results from the Spool SQL Screen grab.

I did notice an ORA error in the spool file

Error: ORA-01476: divisor is equal to zero but I am unsure why this would appear.

Thanks

Lushman
Toad_spool_sql.txt (4.22 KB)

9

Oh, it’s the part of the query where we are dividing by maxblocks to
determine the block size. If I remember correctly, it was a problem in 8i when
you have autoextend disabled on a tablespace.

To find the tablespaces causing the problem, do this query. If you enable
autoextend on them, the problem should go away.

select file_name , tablespace_name , maxblocks

from dba_temp_files

where maxblocks is null

or maxblocks = 0

union

select file_name , tablespace_name , maxblocks

from dba_data_files

where maxblocks is null

or maxblocks = 0

This is fixed in the current beta, and will be fixed in Toad 11.

10

Thanks Jdorlon,

I see what you mean,
Our tablespaces do not have autoextend running on our oracle 8i databases as I believe there was an issue with any datafile exceeding 2gb on an 8i database;

Thanks

Lushman