Friday, July 4, 2008

Table-space Issue...

The User sitescope_user is putting data into user tablespace -- see the query below, this you need to run as sitescope_user only

SQL> select * from user_TS_Quotas;

TABLESPACE_NAME                     BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
USERS                          3.4355E+10          0    4193744          0 NO

SQL> conn/ as sysdba
Connected.

See what table-space has what %ge free:
SQL> select
  2    b.file_id "File #",
  3    substr(b.tablespace_name,1,15) "Tablespace Name",
  4    b.bytes / 1024 "# Kbytes",
  5    ((b.bytes / 1024) - sum(nvl((a.bytes / 1024),0))) "# used",
  6    sum(nvl(a.bytes,0) / 1024) "# free",
  7    trunc((sum(nvl(a.bytes,0))/(b.bytes))*100,3) "%free"
  8  from
  9    sys.dba_free_space a,
10    sys.dba_data_files b
11   where
12   a.file_id(+) = b.file_id
13   group by
14   b.tablespace_name,
15   b.file_id,
16  b.bytes
17  order by
18  b.tablespace_name;

    File # Tablespace Name   # Kbytes     # used     # free      %free
---------- --------------- ---------- ---------- ---------- ----------
         5 EXAMPLE             102400      79232      23168     22.625
         3 SYSAUX              481280     429120      52160     10.837
         1 SYSTEM              501760     500736       1024       .204
         2 UNDOTBS1           5678080     101568    5576512     98.211
         4 USERS             33554416   33553776        640       .001

USER tp has only .001% free !!!

Check what data-files owned by the user "USERS":

SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='USERS';

FILE_NAME
----------------------------------------------------------------------------------------------------
C:\ORACLE\10G\ORADATA\ORCL\USERS01.DBF

Add data file to the "USERS" table-space:

SQL> ALTER TABLESPACE USERS
  2  ADD DATAFILE 'D:\oracle\10g\oradata\orcl\USERS02.DBF' size 5000m
  3  AUTOEXTEND ON
  4  NEXT 500K;

Tablespace altered.

SQL> select
  2    b.file_id "File #",
  3    substr(b.tablespace_name,1,15) "Tablespace Name",
  4    b.bytes / 1024 "# Kbytes",
  5    ((b.bytes / 1024) - sum(nvl((a.bytes / 1024),0))) "# used",
  6    sum(nvl(a.bytes,0) / 1024) "# free",
  7    trunc((sum(nvl(a.bytes,0))/(b.bytes))*100,3) "%free"
  8  from
  9    sys.dba_free_space a,
10    sys.dba_data_files b
11   where
12   a.file_id(+) = b.file_id
13   group by
14   b.tablespace_name,
15   b.file_id,
16  b.bytes
17  order by
18  b.tablespace_name;

    File # Tablespace Name   # Kbytes     # used     # free      %free
---------- --------------- ---------- ---------- ---------- ----------
         5 EXAMPLE             102400      79232      23168     22.625
         3 SYSAUX              481280     429184      52096     10.824
         1 SYSTEM              501760     500736       1024       .204
         2 UNDOTBS1           5678080     101568    5576512     98.211
         4 USERS             33554416   33553776        640       .001
         6 USERS              5120000         64    5119936     99.998

6 rows selected.

**** For better performance add data-files to

SYSTEM
SYSAUX
Table spaces also.-- use the ALTER TABLESPACE ...command above

**** Once in a while execute the query mentioned below to monitor the space usage and hence avoid point-on-time bottle neck [ called Pro-ACTIVE TUNING ;-) ]

SQL> select
  2    b.file_id "File #",
  3    substr(b.tablespace_name,1,15) "Tablespace Name",
  4    b.bytes / 1024 "# Kbytes",
  5    ((b.bytes / 1024) - sum(nvl((a.bytes / 1024),0))) "# used",
  6    sum(nvl(a.bytes,0) / 1024) "# free",
  7    trunc((sum(nvl(a.bytes,0))/(b.bytes))*100,3) "%free"
  8  from
  9    sys.dba_free_space a,
10    sys.dba_data_files b
11   where
12   a.file_id(+) = b.file_id
13   group by
14   b.tablespace_name,
15   b.file_id,
16  b.bytes
17  order by
18  b.tablespace_name;

No comments:

Post a Comment