OraFAQ Forum: Server Administration » dbms_metadata giving incorrect results

Home » RDBMS Server » Server Administration » dbms_metadata giving incorrect results (DB11.2.0.2, Solaris 2.10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_metadata giving incorrect results [message #512491]

Mon, 20 June 2011 12:39

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

nlsb> select file_name,bytes from dba_data_files where tablespace_name='ARIAN_DATA';

FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf     5368709120
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1txnm_.dbf     8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tr6v_.dbf     8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1tonc_.dbf     8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1nq86_.dbf     8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6w8ow60t_.dbf     8589934592
/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf1jhfr_.dbf     8589934592

7 rows selected.

nlsb> select dbms_metadata.get_ddl('TABLESPACE','ARIAN_DATA') from dual;

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

  CREATE TABLESPACE "ARIAN_DATA" DATAFILE
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  '' SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  '' SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 1073741824
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 8589934592
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M,
  SIZE 1073741824
  AUTOEXTEND ON NEXT 1073741824 MAXSIZE 8192M
  NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT NOCOMPRESS  SEGMENT SPACE
 MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6w8ow60t_.dbf' RESIZE 8589934592
   ALTER DATABASE DATAFILE
  '/data950/nlsb/nlsb/datafile/o1_mf_arian_da_6wf27lcn_.dbf' RESIZE 5368709120


nlsb>

dbms_metadata is giving me code that will create more datafiles than the original, and furthermore won't run: firstly, because two files are named with a null string, and secondly because it includes RESIZE commands which won't work because they nominate OMF file names. Am I missing something obvious? Or is dbms_metadata unreliable?
Thank you for any explanation.

Report message to a moderator