Undo DDL statement on View in Oracle

ddllogsoracle

I Want to undo DDL changes on View, how to do that?

I have not set utl_file_dir parameter and can't restart the database to set it. So log miner can not be used yes? it needs this parameter… If there is any chance to use logminer without setting this parameter(without restarting database) ?

Waiting you suggestions, thank you

Best Answer

Depending on the version of Oracle and whether you have the appropriate privileges (FLASHBACK ANY TABLE) and if the change was relatively recent,

SELECT text
  FROM dba_views AS OF TIMESTAMP( systimestamp - interval '1' hour )
 WHERE owner = <<owner of view>>
   AND view_name = <<name of view>>

will give you the text of the view definition as of an hour ago. You can obviously adjust the flashback time to a timestamp just before the DDL you want to undo.

Related Solutions

How to allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.

The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly. Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables. Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.

Eliminate database fragmentation (export, drop and re-import tables)

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Best Answer

Related Solutions

How to allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

Eliminate database fragmentation (export, drop and re-import tables)

Related Question