In 12.5.0.10 i get error on funktion "Minimum size" for Datafile

Toad for Oracle - Beta
1

Using 12.5.x.x beta i get error “You ust have the SELECT privilege on DBA_DATA_FILES, … to use this feature”,

but using an older version of TOAD like 12.1 against the same database

then i get the Result.

I often use this feature and i wish to use this in 12.5 without changing the privileges.

Tanks.

Felix

2

There are a few ways to get to that feature in Toad. Please tell me which windows you are opening and what you click on. Also, in Toad 12.1, please turn on spool sql (from main menu -> Database -> Spool SQL -> Spool to screen) to capture the SQL that Toad executes and send that to me. Thanks.

3

Hallo John,

I know 3 ways to get the function “Minimum size” for datafiles:

  1. Tablespaces Space select (F4) (dc) datafile

  2. Tablespaces Files select (dc) datafile

  3. Schema Browser Tablespaces select (dc) datafile

(dc) = doubleclick

12.5.: All ways lead me to the same Message (select privilege).

12.1.: All ways give me an result value.

Now sample : Using (3) via Schema Browser, Tablespaces, datafile

12.1:

Session: SYSTEM@EPBW.WORLD

Timestamp: 12:49:45.037

Select bytes, file_id, bytes / blocks

from dba_data_files

where file_name = :fname

:fname(VARCHAR[46],IN/OUT)=’+EPBW_DATA/epbw/datafile/work64.1128.817720527’

Session: SYSTEM@EPBW.WORLD

Timestamp: 12:49:45.109

Select sum(bytes)

from dba_free_space

where tablespace_name = :tsn

and file_id = :fid

and block_id >= nvl((Select (block_id + (bytes/8192))

from dba_extents

where block_id = (Select max(block_id)

from dba_extents

where file_id = :fid

and tablespace_name = :tsn)

and file_id = :fid

and tablespace_name = :tsn), 0)

:tsn(VARCHAR[6],IN/OUT)=‘WORK64’

:fid(INTEGER,IN/OUT)=144

—> Datafile can be shrunk to a minimum size of apporximatly 1 MB.

12.5.:

Session: SYSTEM@EPBW.WORLD

Timestamp: 12:54:45.332

Select bytes, file_id, bytes / blocks

from dba_data_files

where file_name = :fname

:fname(VARCHAR[46],IN/OUT)=’+EPBW_DATA/epbw/datafile/work64.1128.817720527’

Session: SYSTEM@EPBW.WORLD

Timestamp: 12:54:45.422

Select sum(bytes)

from dba_free_space

where tablespace_name = :tsn

and file_id = :fid

and block_id >= nvl((Select (block_id + (bytes/8192))

from dba_extents

where block_id = (Select max(block_id)

from dba_extents

where file_id = :fid

and tablespace_name = :tsn)

and file_id = :fid

and tablespace_name = :tsn), 0)

:tsn(VARCHAR[6],IN/OUT)=‘WORK64’

:fid(INTEGER,IN/OUT)=144

–> Message

Best Regards

Felix

4

Thanks for the details. I will have this fixed in the next beta.

5

Thanks,

now there is the result like i had expected.

Regards

Felix