Just gona post my query I just used now. It’s incomplet an dirty now.
If somebody has better version of it, please poste it.
The query just work for index tablespace (also with partitioned and subpartitioned indexes)
<code>select ‘alter ‘||decode(segment_type,’INDEX’,'INDEX’,'INDEX PARTITION’,'INDEX’,'INDEX SUBPARTITION’,'INDEX’)||’ ‘||
owner||'.'||segment_name||' rebuild '||
decode(segment_type,'INDEX','','INDEX PARTITION','partition','INDEX SUBPARTITION','subpartition')||' '||partition_name||';' cmd
from rim_dba_extents
where (file_id,block_id) in (select file_id,block_id from
(select file_id,block_id from rim_dba_extents sde3
where file_id in (select file_id
from dba_data_files
where tablespace_name like 'Tablespace_name%'
and sde3.block_id>(select min(BLOCK_ID)+1 from dba_free_space where file_id=sde3.file_id)
)
order by block_id)
where rownum < 100)
</code>
btw: I use a copy of dba_extents becuase in 10.2.0.4 is bug which makes dba_extents extremly slow.
create table rim_dba_extents as select * from dba_extents;
drop table rim_dba_extents;
If you DUAL table hast multiple rows inserted, and you select from the table, just one row is visible.
But if you do a merge statement all rows might be returned.
See: Doc ID: 728710.1
Here is a quick guide how to find out if your dual has multiple rows
SQL> analyze table dual compute statistics;
SQL> select num_rows from dba_tables where table_name=’DUAL’;
2
Solve the problem
truncate table dual;
insert into dual values(’x');
thats it.
ahh.. btw. the error you might get with “merge into … using dual …” are constraint or unique index voliations because of multplie inserts.
Is always annoying to resize a datafile with empty space but some blocks occupied at the end of the file.
You get the
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
There are may brute force ways to solve this as rebuild or move everthing in the tablespace or exp/imp.
Sometimes you just want resize one datafile to archive some monitoring limits with no impact to production.
Find the last object in the datafiles for a specific tablespace (the is slow, checkout Metalink Note 422730)
select owner,segment_name,partition_name,segment_type
from dba_extents
where (file_id,block_id) in (select file_id,max(block_id) from dba_extents group by file_id)
and tablespace_name like 'INDEX_TBSP_NAME%'
If you have the object, you can do:
Alter table move; -- ! table unavailable, indexes need to be rebuild
or
alter index rebuild; -- index unavailable
or
alter index rebuild online; -- index available during rebuild
I searched some time to find the documentation which tell how to install a patch on a data guard configuration with a pysical standby.
You need this tow pieces of infomation:
- http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rollup.htm#i1029537
- Metalink Doc ID: 278641.1
My short summary for 10.2.0.3 to 10.2.0.4 upgrade:
1. primary: shutdown immedaite
2. primary: lsnrctl stop
3. standby: recover managed standby database cancel;
4. standby: shutdown immediate
5. standby: lsnrctl stop
6. primary: opatch apply
7. standby: opatch apply
8. standby: starup mount
9. standby: recover managed standby database nodelay disconnect;
10. primary: startup upgrade
11. primary: check log shipping, v$archive_dest.status
12. primary: alter system archive log current;
13. primary: select dest_id, status from v$archive_dest;
14. primary:
# disconnect & reconnet
SPOOL upgrade_info.log
@?/rdbms/admin/utlu102i.sql
SPOOL OFF
SPOOL patch.log
@?/rdbms/admin/catupgrd.sql
SPOOL OFF
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
15. alter system archive log current;
16. primary: shutdown / startup
17. standby: select max(sequence#) from v$log_history; — check recovery works