Skip to Main Content
  • Questions
  • Restricting datafile addition to a filesystem (ASM)

Breadcrumb

May 4th

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.

Is this answer out of date? If it is, please let us know via a Comment

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