Friday, May 29, 2009

TEMP tablespace usage

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

Thursday, May 21, 2009

Shrinking datafiles.

# PERMANENT + UNDO usage
select dt.contents, sum(bytes)
from dba_data_files ddf, dba_tablespaces dt
where ddf.tablespace_name = dt.tablespace_name
group by dt.contents;

# TEMP tablespace
select 'TEMP',sum(bytes) from dba_temp_files;

# PERM FREE
select 'PERM FREE',sum(bytes)
from dba_free_space dfs, dba_tablespaces dt
where dfs.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT';

# PERM USED
select 'PERM USED',sum(bytes)
from dba_segments ds, dba_tablespaces dt
where ds.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT';

# READ ONLY
select sum(bytes)
from dba_data_files ddf, dba_tablespaces dt
where ddf.tablespace_name = dt.tablespace_name
and dt.status = 'READ ONLY';

# shrink files with no fragmentation
select 'alter database datafile '''||file_name||''' resize '||sum((ddf.bytes-dfs.bytes)/1024)||'K;'
from dba_data_files ddf,
dba_free_space dfs
where ddf.file_id = dfs.file_id
and ddf.tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'PERMANENT')
group by ddf.file_name
having count(*) = 1
order by sum((ddf.bytes-dfs.bytes)/1024)+64 desc;

# shrink fragmented files down to the last block
create table max_block as select file_id, max(block_id+blocks-1) block from dba_extents group by file_id;

select 'alter database datafile '''||ddf.file_name||''' resize '||mb.block*dt.block_size/1024||'K;'
from
dba_data_files ddf,
dba_tablespaces dt,
max_block mb
where ddf.file_id = mb.file_id
and ddf.tablespace_name = dt.tablespace_name
and dt.contents = 'PERMANENT'
order by mb.block*dt.block_size/1024 desc;

Thursday, May 7, 2009

Monitor rollback operations

# From http://www.oracle.com/technology/pub/articles/10gdba/week2_10gdba.html

SELECT USED_UREC
FROM V$TRANSACTION;

select time_remaining
from v$session_longops
where sid = ;

SELECT SQL_TEXT
FROM V$SQL
WHERE SQL_ID = ;

select rcvservers from v$fast_start_transactions;

# check parameters
RECOVERY_PARALLELISM
ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH

Friday, May 1, 2009

EMD upload error: Upload was successful but collections currently disabled - disk full

-bash-3.00$ emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload was successful but collections currently disabled - disk full

#From Metalink note 317242.1

#Cause
The EMD disk system shows used percent at 99%.
The agent default for space required for upload files is 98%. The collections will stop when the
space on the disk is beyond that.


#Solution
To implement the solution, please execute the following steps:

1. Update the AGENT OH/sysman/config/emd.properties with the parameter:
uploadMaxDiscUsedPct=99
uploadMaxDiskUsedPctFloor=99
2. Stop the agent: emctl stop agent
3. Start the agent: emctl start agent
4. Upload to the OMS: emctl upload