Re: newbie q: DBMS_SPACE_ADMIN package

In ALL_TAB_PRIVS (or DBA_TAB_PRIVS) you query grants given on all type of objects (not only tables as the name suggests but also packages, procedures etc.).

Instead query DBA_OBJECTS to see what objects exists. DBMS_SPACE_ADMIN is installed in the database by running catproc.sql, what you probably already did when you created the database (or is done by the Database Assistant). It exists since 8i. Users with DBA privs can execute it (because they have the "execute any procedure" system privilege). Personally I don't think others should be granted execute priv on this package since its actions are typical dba tasks.

You need it when you want to convert a classic dictionary managed tablespace to a locally managed tablespace or vice versa and for some (rare) maintenance tasks.
But I advice you to create a tablespace as LMT instead of converting it whenever you can.

In the future please specify platform and Oracle version whenever you post a question in this newsgroup.

Vince Laurent <vlaurent_at_NOSPAM.networkusa.net> schreef in berichtnieuws nbaosu8kb72spcbk0r4nbe4quaoinl80gb_at_4ax.com...
| According the the documentation: "DBMS_SPACE_ADMIN is a built-in
| PL/SQL package that Oracle provides to help you maintain the integrity
| of locally managed tablespaces."
|
| I ran a query:
| SQL> SELECT table_name, grantee
| 2 FROM all_tab_privs
| 3 WHERE grantor='SYS' and privilege='EXECUTE'
| 4 ORDER BY table_name;
|
| to see what packages were installed. This on was not. Does it NOT
| install by default? What if I don't have locally managed tablespaces,
| would I need it?
|
| Thanks!
| Vince
Received on Sat Nov 09 2002 - 15:12:36 CST