Check for database space issues
Find information about tablespace space
The DBA_FREE_SPACE data dictionary view shows the remaining space of a tablespace. This example shows the amount of free space for extents in tablespace 'EDACS01':
SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'EDACS01';
TABLESPACE_NAME FILE_ID BYTES BLOCKS ------------------------------ ---------- ---------- ---------- EDACS01 5 396296192 48376
Check the segment size and number of used extends in a particular schema
The following query returns the name, size and number of extends of each table in schema 'EDACSADMIN'. It shows which table is extending most heavily.
SET LINE 120 COL TABLENAME FORMAT a20 SELECT SEGMENT_NAME "TABLENAME", TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' AND OWNER='EDACSADMIN' ORDER BY EXTENTS DESC;
TABLENAME TABLESPACE_NAME BYTES BLOCKS EXTENTS -------------------- ------------------------------ ---------- ---------- ---------- EDACS_MAINLOG EDACS01 22020096 2688 21 EDACS_USERS EDACS01 1048576 128 16 EDACS_REMOTE EDACS01 1048576 128 16 EDACS_ROUTER EDACS01 1048576 128 16 EDACS_DAYSTATS EDACS01 1048576 128 16 EDACS_VERSION EDACS01 1048576 128 16 EDACS_MONSTATS EDACS01 1048576 128 16 EDACS_SERVICE EDACS01 1048576 128 16 EDACS_TEMPLOG EDACS01 2097152 256 2
The same request for indizes. It shows which index is extending most heavily.
SET LINE 120 COL INDEX FORMAT a20 SELECT SEGMENT_NAME "INDEX", TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX' AND OWNER='EDACSADMIN' ORDER BY EXTENTS DESC; INDEX TABLESPACE_NAME BYTES BLOCKS EXTENTS -------------------- ------------------------------ ---------- ---------- ---------- MAINLOG_PK EDACS01 20971520 2560 20 TEMPLOG_PK EDACS01 20971520 2560 20 MONSTATS_PK EDACS01 1048576 128 16 VERSION_PK EDACS01 1048576 128 16 D_02 EDACS01 11534336 1408 11 D_01 EDACS01 9437184 1152 9 D_03 EDACS01 8388608 1024 8 USERS_PK EDACS01 5242880 640 5 REMOTE_PK EDACS01 5242880 640 5 ROUTER_PK EDACS01 5242880 640 5 SERVICE_PK EDACS01 5242880 640 5 DAYSTATS_PK EDACS01 2097152 256 2
The following script checksize.sql checks for the remaining free space in all tablespaces
select
a.file_id,
substr(a.tablespace_name,1,14) tablespace_name,
trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
trunc(a.bytes/1024/1024) size_mb,
trunc(a.maxsize/1024/1024) maxsize_mb,
a.autoextensible ae,
trunc(decode (a.autoextensible,'YES', (a.maxsize-a.bytes+b.free)/a.maxsize*100,'NO',b.free/a.maxsize*100 ) ) free_pct
from
(select file_id,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes) maxsize
from dba_data_files
group by file_id,
tablespace_name,
autoextensible,
bytes,
decode(autoextensible,'YES',maxbytes,bytes)) a,
(select file_id,
tablespace_name,
sum(bytes) free
from dba_free_space
group by file_id,
tablespace_name) b
where a.file_id=b.file_id(+)
and a.tablespace_name=b.tablespace_name(+)
order by a.tablespace_name asc;
SQL> @checksize.sql
FILE_ID TABLESPACE_NAM FREE_MB SIZE_MB MAXSIZE_MB AE FREE_PCT
---------- -------------- ---------- ---------- ---------- --- ----------
5 EDACS01 377 500 500 NO 75
3 SYSAUX 32636 150 32767 YES 99
1 SYSTEM 32550 300 32767 YES 99
2 UNDOTBS1 32761 125 32767 YES 99
4 USERS 32767 5 32767 YES 99
Reorganizing Oracle Indizes
In order to reduce the number of extents, whe recreate the index by setting the initial extent to the current index size. The current index size is found by:
SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'D_01'; SUM(BYTES) ---------- 9437184 DROP INDEX D_01; create index d_01 on edacs_mainlog(service, start_date) tablespace edacs01
storage(initial 20m next 10m pctincrease 0);
Reorganising Oracle Tables
For reorganizing tables, it is best to use a new tablespace. Then using the MOVE clause in the ALTER TABLE allows to change the tablespace and/or storage parameters of an Oracle table. Add the word ONLINE to the syntax and the table can be moved even while users are updating it.
/* ******************************************************************* ** cr_tblspc.sql: creates tablespace for e-dacs ** ** example: ** create tablespace edacs01 datafile '<path-to-dbfile>' size 500 M; */ create tablespace edacs02 datafile '/u02/oradata/work2/edacsdb02.dbf' size 500 M; ALTER TABLE EDACS_USERS MOVE
TABLESPACE EDACS02
STORAGE (INITIAL 8m NEXT 8m PCTINCREASE 0);
Alternatively, by using the SQL statement 'CREATE TABLE AS SELECT' we can also change the storage parameters for a table (INITIAL, NEXT, FREELISTS) by making a table copy, then dropping the original and remaning the copy to original.
create table EDACS_USERS_NEW tablespace EDACS02 storage (initial 8m next 8m pctincrease 0) as select * from EDACSADMIN.EDACS_USERS order by USERNAME;
If the flashback feature was not disabled, dropping a table in Oracle 10g just renames and puts it into the recycle bin. The object is now visible as something like "BIN$P7xjEd08uMfgQFVGYRAoxw==$0". Remove it with 'purge dba_recyclebin;' forever. Dropping a table also drops dependent objects such as primary keys and indizes. They have to be rebuild. The oldfasioned way is to use export/import to rebuild the tables from scratch.
oracle@debbie:~$ exp edacsadmin/xxx@work2 file=edacs_user_tbl.dmp tables=edacs_users
Export: Release 10.1.0.2.0 - Production on Sun Nov 25 16:46:59 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.1.0.2.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EDACS_USERS 740 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.