Enterprise Manager 12c overview

I've been to a demo-day in Hilversum. Really cool stuff. 
This movie will give a short overview of EM12c:

Flashback package source in Oracle 9.2

A developer came to me that he messed up his package source he was working on.

He asked me if we could retrieve it from backup, preferable from an hour ago. This is typical a flashback scenario, but he was working on 9.2. Is this a problem? No, we can use Flashback Query in 9.2. We just have to query the dba_source view.

The database (development) is not running in archivemode, so we can flash back to any point within the redo logs. This time we flash back to about half an hour ago.

The output is saved in a spool file.

ALTER SESSION 
   SET NLS_TIMESTAMP_FORMAT = 
   'DD-MON-YYYY HH24:MI:SS.FF3';

set lines 9999

set trimspool on

set pages 9999

set heading off

spool old_version.sql

select text from dba_source  

       as of timestamp to_timestamp('22-SEP-2011 07:30:00.000')

 where owner='SCOTT'

   and name='MYPACKAGE'

 order by type, line;

spool off

Secure Files

Today I have been playing with Secure files. Arup Nanda has an excellent article on this:

Oracle Database 11g: The Top New Features for DBAs and Developers | SecureFiles

Some notes on installing 10.2.0.4 on SLES 11 64bits

When installing this unsupported combination, some solutions might be:


- Change the /etc/SuSe-release: backup the original file and change it to SuSe 9.
- error invoking target of makefile ins_ctx.mk --> install gcc43-32bit
- error on ins_sysman.mk --> unset LD_LIBRARY_PATH
- error on ins_emdb.mk -->ignore this error
 Much more can be found on http://www.puschitz.com/InstallingOracle10g.shtml and MOS (My Oracle Support).

Move tables, indexes and lobs to another tablespace

On many occasions, I have been using a script from Tom Kyte to move all objects from one tablespace to another. This script also gets all the indexes of the tables that are being moved. After moving the table, you should rebuild the index.
The script has been slightly adapted by me, but the main code is by Tom.

We do need to purge the recyclebin, otherwise you will end up with moving BIN$ objects in your script. What remains sometimes, are the lob segments. These can be moved with:

alter table owner.table move lob(columnname) store as (tablespace newtablespace);

This has been added in a second part of the query. So the total scripting is like below. You may call it with @moveall USERS USERS2 if you want everything to move to the USERS2 tablespace. Note that it only creates a script, the actual moving has to be done and can take quite a while.

set echo off
prompt Enter a value for the OLD tablespace:
define old_tablespace=&1
prompt Enter a value for the NEW tablespace:
define new_tablespace=&2


column order_col1 noprint
column order_col2 noprint
set heading off verify off feedback off echo off
set pages 0 lines 9999 trimspool on
purge dba_recyclebin;

spool tmp.sql

select decode( segment_type, 'TABLE', segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || owner || '.' || segment_name || decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
' tablespace &&new_tablespace ' || ';'
from dba_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
and tablespace_name = '&&old_tablespace'
order by owner, order_col1, order_col2
/


select 'alter table ' || owner || '.' || table_name ||
' move lob(' || column_name || ') store as (tablespace &&new_tablespace);'
from dba_lobs where TABLESPACE_NAME='&&old_tablespace'
/


spool off


set heading on verify on feedback on echo on

REM Now you can:
REM 1. Backup your database
REM 2. Start it restricted if possible
REM 3. Run the generated script


Use it with care, make sure you have a database backup and start up the database in restricted mode if possible.

After the move, check for unusable indexes and invalid objects. 

Drop tablespace?
If you think the old tablespace is empty, you can use drop tablespace without error. If it's not fully empty, it will reply:

*
ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

So in my opinion, it's better to use

select FILE_NAME from dba_data_files 
where TABLESPACE_NAME='';

drop tablespace ;

and delete the files afterwards.


Addendum.
The following query I recently used for checking empty tablespaces. It works pretty well, and also makes sure that empty, but yet defined, tables will not be dropped.

select t.tablespace_name
from dba_tablespaces t
where contents='PERMANENT'
minus
select distinct tablespace_name from (
select tablespace_name from dba_segments
union select tablespace_name from dba_tables
union select tablespace_name from dba_tab_partitions
union select tablespace_name from dba_tab_subpartitions
union select tablespace_name from dba_indexes
union select tablespace_name from dba_ind_partitions
union select tablespace_name from dba_ind_subpartitions
union select tablespace_name from dba_lobs
union select tablespace_name from dba_lob_partitions
union select tablespace_name from dba_lob_subpartitions
)
;

Final note
Please use this post carefully. You are totally responsible for dropping things that should not be dropped. Make sure you proofread the scripts, create backups and think for yourself.

Export large database without using much diskspace

I have one pretty large database running in one of our development environments. I was wondering if this Oracle 8i database could be easily exported. I didn't have much space for export files, so I used compression during export with a script a found  at http://www.tc.umn.edu/~hause011/code/exp-imp-db.ksh.




How much compression would it give me and how long would it take?


Here are some results:


Database 8.1.7.4 on AIX 5.
Export running 40 hours
database size: 236 Gb
export dumpfile (gzip) 16Gb


Import would take considerably longer, because indexes would need te be created. I would guess around a week. The export time will probably not have been much shorter without compression.

explain plan easy!

EXPLAIN PLAN FOR SELECT * FROM MYTABLE; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);