Skip to Main Content
  • Questions

  • Restricting datafile addition to a filesystem (ASM)

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Ashok.

Asked: July 21, 2016 - 9:26 am UTC

Last updated: July 22, 2016 - 7:30 pm UTC

Version: 11g,12c

Viewed 1000+ times

You Asked

Hi Tom
The normal method of adding datafiles to a tablespace is to login to the database as "sqlplus / as sysdba" and then executing the "alter tablespace add datafile.." command. Is there a way to prevent / prompt the DBAs from accidentally adding a datafile to another location/filesystem other than the one that has been identified for datafiles. For example, in ASM if the DBA accidentally omits "+" in +DATA or specifies some other path, the database/system should either prevent this command or error out or prompt for confirmation. I do not know what kind of solution is needed to prevent these type of "user errors" in real world systems.

Thanks & Warm Regards
Ashok

and Connor said...

Take a look at using a DDL trigger to extract the DDL being run:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:267415465220

and then examine the DDL to see if its adding a datafile and then examine the string for the right path name.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library