Oracle – SQL information and quick database health checks

by Frank4DD, @2004

Check for database space issues

1. 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

   
   
2. 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

   
   
3. The following script 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

   
   
4. 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);

5. Reorganising Oracle Tables

For reorganizing, 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.