DROPED DATA DICTIONARY VIEWS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DROPED DATA DICTIONARY VIEWS

  1. #1
    Join Date
    Aug 2001
    Location
    cuddapah
    Posts
    145
    !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 .....



  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what user ran catalog?

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

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Run this as SYS:

    Code:
    -- 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
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width