DROPED DATA DICTIONARY VIEWS

[smdakram]

!hello

In Oracle 9i I Drop DATA DICTIONARY VIEW DBA_DATA_FILES by using normal SQL command DROP .

SQL> DROP VIEW DBA_DATA_FILES;

view drop.

After that when i try to select the view but i got these error::

SQL> select * from dba_data_files
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

===============

I SHUTDOWN the Database and again i run CATALOG.SQL (script)
but still iam getting these ERROR

HOW TO SLOVE THESE PROBLEM ??

Can u tell .....

[pando]

what user ran catalog?

have you checked dba_views, dba_synonyms and see if it exists?

[julian]

Run this as SYS:

-- Start of DDL script for DBA_DATA_FILES
-- View DBA_DATA_FILES

CREATE OR REPLACE VIEW dba_data_files (
   file_name,
   file_id,
   tablespace_name,
   bytes,
   blocks,
   status,
   relative_fno,
   autoextensible,
   maxbytes,
   maxblocks,
   increment_by,
   user_bytes,
   user_blocks )
AS
select v.name, f.file#, ts.name,
       ts.blocksize * f.blocks, f.blocks,
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
       ts.blocksize * f.maxextend, f.maxextend, f.inc,
       ts.blocksize * (f.blocks - 1), f.blocks - 1
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
where v.file# = f.file#
  and f.spare1 is NULL
  and f.ts# = ts.ts#
union all
select
       v.name,f.file#, ts.name,
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
       f.relfile#,
       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts
where v.file# = f.file#
  and f.spare1 is NOT NULL
  and v.file# = hc.ktfbhcafno
  and hc.ktfbhctsn = ts.ts#
/

COMMENT ON TABLE dba_data_files IS 'Information about database data files'
/

-- Comments for DBA_DATA_FILES

COMMENT ON COLUMN dba_data_files.autoextensible IS 'Autoextensible indicator:  "YES" or "NO"'
/
COMMENT ON COLUMN dba_data_files.blocks IS 'Size of the file in ORACLE blocks'
/
COMMENT ON COLUMN dba_data_files.bytes IS 'Size of the file in bytes'
/
COMMENT ON COLUMN dba_data_files.file_id IS 'ID of the database data file'
/
COMMENT ON COLUMN dba_data_files.file_name IS 'Name of the database data file'
/
COMMENT ON COLUMN dba_data_files.increment_by IS 'Default increment for autoextension'
/
COMMENT ON COLUMN dba_data_files.maxblocks IS 'Maximum size of the file in ORACLE blocks'
/
COMMENT ON COLUMN dba_data_files.maxbytes IS 'Maximum size of the file in bytes'
/
COMMENT ON COLUMN dba_data_files.relative_fno IS 'Tablespace-relative file number'
/
COMMENT ON COLUMN dba_data_files.status IS 'File status:  "INVALID" or "AVAILABLE"'
/
COMMENT ON COLUMN dba_data_files.tablespace_name IS 'Name of the tablespace to which the file belongs'
/
COMMENT ON COLUMN dba_data_files.user_blocks IS 'Size of the useful portion of file in ORACLE blocks'
/
COMMENT ON COLUMN dba_data_files.user_bytes IS 'Size of the useful portion of file in bytes'
/

-- Grants for DBA_DATA_FILES

GRANT SELECT ON dba_data_files TO select_catalog_role
/

-- End of DDL script for DBA_DATA_FILES