SQL Language (MOSC)

MOSC Banner

REGEXP_REPLACE to extract just the diskgroup name from a file name (regular expression help)

edited Jan 29, 2013 4:49AM in SQL Language (MOSC) 9 commentsAnswered ✓
Hi,
I'm wondering if anyone out there is really good with regular expression syntax and can fill in the regular express to extract just the ASM diskgroup name from an ASM datafile name.

For example on Oracle 10g DBMS_METADATA.GET_DDL returns the full file name when extracting tablespace DDL (note 11g does not):

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '+DATA/orcl/datafile/users.271.882125073' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192


What I'd like to do is wrap a REGEXP_REPLACE around that to make the file name just '+DATA'.  Basically just the diskgroup name, still in the single quotes.  The "/" slash to the end of the file name (everything with the grey background) removed.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center