修改users表空间数据文件操作

#su - zyoracle
[YOU HAVE NEW MAIL]
$ df -gt
Filesystem    GB blocks      Used      Free %Used Mounted on
/dev/hd4           5.00      2.99      2.01   60% /
/dev/hd2           5.00      2.42      2.58   49% /usr
/dev/hd9var        2.00      0.46      1.54   23% /var
/dev/hd3           2.00      0.14      1.86    7% /tmp
/dev/fwdump        1.00      0.00      1.00    1% /var/adm/ras/platform
/dev/hd1           0.25      0.00      0.25    1% /home
/proc                 -         -         -    -  /proc
/dev/hd10opt       1.00      0.48      0.52   48% /opt
/dev/zy_arc       50.00      2.74     47.26    6% /zyarchive
/dev/zy_ora      490.00    244.95    245.05   50% /zyhome
/dev/zy_redo       5.00      1.95      3.05   40% /zyredo
$ cd jyc

$ cat tbs.sql
set line 132
set wrap off
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 18 13:42:16 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba
Connected.
SQL> @tbs.sql

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
EXAMPLE                              136.88      17520        136.57        99.77           .31
XDB                                      45       5760         44.75        99.44           .25
TOOLS                                 31.25       4000         29.56        94.59          1.69
USERS                                 98304   12582912         89486        91.03          8818
CWMLITE                                  20       2560            15           75             5
DRSYS                                    20       2560          9.69        48.45         10.31
ODM                                      20       2560          9.56         47.8         10.44
UNDOTBS1                               4000     512000          1526        38.15          2474
KCGL                                   1000     128000        361.44        36.14        638.56
PERFSTAT                                500      64000        162.81        32.56        337.19
SYSTEM                                 2000     256000        601.94         30.1       1398.06

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                  11650    1491200             4          .03         11646
INDX                                    500      64000           .12          .02        499.88

13 rows selected.

SQL> desc dba_data_files;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 FILE_NAME                                                                         VARCHAR2(257)
 FILE_ID                                                                           NUMBER
 TABLESPACE_NAME                                                                   VARCHAR2(30)
 BYTES                                                                             NUMBER
 BLOCKS                                                                            NUMBER
 STATUS                                                                            VARCHAR2(9)
 RELATIVE_FNO                                                                      NUMBER
 AUTOEXTENSIBLE                                                                    VARCHAR2(3)
 MAXBYTES                                                                          NUMBER
 MAXBLOCKS                                                                         NUMBER
 INCREMENT_BY                                                                      NUMBER
 USER_BYTES                                                                        NUMBER
 USER_BLOCKS                                                                       NUMBER

SQL> col file_name for a50
SQL> select file_name,sum(bytes)/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='USERS' group by file_name,AUTOEXTENSIBLE;

FILE_NAME                                          SUM(BYTES)/1024/1024/1024 AUT
-------------------------------------------------- ------------------------- ---
/zyhome/oracle/oradata/ossdb1/users01.dbf                                 16 YES
/zyhome/oracle/oradata/ossdb1/users02.dbf                                 16 NO
/zyhome/oracle/oradata/ossdb1/users03.dbf                                 16 NO
/zyhome/oracle/oradata/ossdb1/users04.dbf                                 16 NO
/zyhome/oracle/oradata/ossdb1/users05.dbf                                 16 NO
/zyhome/oracle/oradata/ossdb1/users06.dbf                                 16 NO

6 rows selected.
set timing on
alter database datafile '/zyhome/oracle/oradata/ossdb1/users01.dbf' resize 20480M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users02.dbf' resize 20480M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users03.dbf' resize 20480M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users04.dbf' resize 20480M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users05.dbf' resize 20480M;
alter database datafile '/zyhome/oracle/oradata/ossdb1/users06.dbf' resize 20480M;

set timing on
alter  tablespace  users add datafile '/zyhome/oracle/oradata/ossdb1/users08.dbf' size 20480M ;

Tablespace altered.

SQL> @tbs.sql

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
EXAMPLE                              136.88      17520        136.57        99.77           .31
XDB                                      45       5760         44.75        99.44           .25
TOOLS                                 31.25       4000         29.56        94.59          1.69
CWMLITE                                  20       2560            15           75             5
USERS                                122880   15728640         89486        72.82         33394
DRSYS                                    20       2560          9.69        48.45         10.31
ODM                                      20       2560          9.56         47.8         10.44
UNDOTBS1                               4000     512000       1577.06        39.43       2422.94
KCGL                                   1000     128000        361.44        36.14        638.56
PERFSTAT                                500      64000        162.81        32.56        337.19
SYSTEM                                 2000     256000        601.94         30.1       1398.06

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                  11650    1491200             4          .03         11646
INDX                                    500      64000           .12          .02        499.88

13 rows selected.

Elapsed: 00:00:00.07
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
$ df -gt
Filesystem    GB blocks      Used      Free %Used Mounted on
/dev/hd4           5.00      2.99      2.01   60% /
/dev/hd2           5.00      2.42      2.58   49% /usr
/dev/hd9var        2.00      0.46      1.54   24% /var
/dev/hd3           2.00      0.14      1.86    7% /tmp
/dev/fwdump        1.00      0.00      1.00    1% /var/adm/ras/platform
/dev/hd1           0.25      0.00      0.25    1% /home
/proc                 -         -         -    -  /proc
/dev/hd10opt       1.00      0.48      0.52   48% /opt
/dev/zy_arc       50.00      3.13     46.87    7% /zyarchive
/dev/zy_ora      490.00    268.95    221.05   55% /zyhome
/dev/zy_redo       5.00      1.95      3.05   40% /zyredo