Add oracle tablespace

# Log on to the Database Host using an oracle user (ssh or telnet)
$ Ssh oracle @ Database Host IP
# Log on to the database as sysdba
$ Sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.4.0-Production on Mon Oct 10 15:53:45 2011
Copyright (c) 1982,200 7, Oracle. All Rights Reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
# Run the following statement to check the space attributes: Check whether the attribute type is BIG.
SQL> select * From v $ tablespace;
TS # NAME INC BIG FLA ENC
----------------------------------------------------
0 SYSTEM YES NO YES
1 UNDO YES NO YES
2 SYSAUX YES NO YES
3 TEMP NO YES
4 TOOLS YES NO YES
5 TBS_USER_DAT YES NO YES
6 TBS_USER_IDX YES NO YES
7 TBS_RMAN YES NO YES
8 TEST_MU YES NO YES
9 rows selected.
SQL & gt; set line 110
SQL> set wrap off
# Run the following statement to view the data files corresponding to the tablespace:
SQL> select ts #, name from v $ datafile;
TS # NAME
---------- Success -------------------------------------------------------------------------------------------------------------
0/opt/oracle/oradata/SOC/system01.dbf
1/opt/oracle/oradata/SOC/rbs01.dbf
2/opt/oracle/oradata/SOC/sysaux01.dbf
4/opt/oracle/oradata/SOC/tools01.dbf
5/opt/oracle/oradata/SOC/udata01.dbf
6/opt/oracle/oradata/SOC/uindex01.dbf
7/opt/oracle/oradata/SOC/rman01.dbf
8/opt/oracle/oradata/SOC/test_mu01.dbf
8 rows selected.
SQL>
# For tablespaces (such as TOOLS tablespaces) of non-large file types (the value of the BIG field is NO), there are two ways to expand the tablespaces:
#1. Add a new data file
SQL> alter tablespace tools add datafile '/opt/oracle/oradata/SOC/tools02.dbf' size 10 M;
Tablespace altered.
SQL>
# View the added data file after adding the file:
SQL> select ts #, round (bytes/1024/1024), name from v $ datafile order by ts #;
TS # ROUND (BYTES/1024/1024) NAME
------------------------------------------------------------------------------------------------------------
0 512/opt/oracle/oradata/SOC/system01.dbf
1 32/opt/oracle/oradata/SOC/rbs01.dbf
2 256/opt/oracle/oradata/SOC/sysaux01.dbf
4 128/opt/oracle/oradata/SOC/tools01.dbf
4 10/opt/oracle/oradata/SOC/tools02.dbf
5 1000/opt/oracle/oradata/SOC/udata01.dbf
6 500/opt/oracle/oradata/SOC/uindex01.dbf
7 256/opt/oracle/oradata/SOC/rman01.dbf
8 100/opt/oracle/oradata/SOC/test_mu01.dbf
9 rows selected.
SQL>
#2. Expand the original data file
SQL> alter database datafile '/opt/oracle/oradata/SOC/tools02.dbf' resize 20 M;
Database altered.
# Expand/opt/oracle/oradata/SOC/tools02.dbf to 20 mb and check again:
SQL> select ts #, round (bytes/1024/1024), name from v $ datafile order by ts #;
TS # ROUND (BYTES/1024/1024) NAME
------------------------------------------------------------------------------------------------------------
0 512/opt/oracle/oradata/SOC/system01.dbf
1 32/opt/oracle/oradata/SOC/rbs01.dbf
2 256/opt/oracle/oradata/SOC/sysaux01.dbf
4 128/opt/oracle/oradata/SOC/tools01.dbf
4 20/opt/oracle/oradata/SOC/tools02.dbf
5 1000/opt/oracle/oradata/SOC/udata01.dbf
6 500/opt/oracle/oradata/SOC/uindex01.dbf
7 256/opt/oracle/oradata/SOC/rman01.dbf
8 100/opt/oracle/oradata/SOC/test_mu01.dbf
9 rows selected.
SQL>
# For tablespaces of the large file type (the value of the BIG field is YES), only the original data files can be expanded:
# For example, The TBS_USER_DAT and TBS_USER_IDX tablespaces in the following environment are large file-type tablespaces.
SQL> select * from v $ tablespace;
TS # NAME INC BIG FLA ENC
----------------------------------------------------
0 SYSTEM YES NO YES
1 UNDO YES NO YES
2 SYSAUX YES NO YES
3 TEMP NO YES
4 TOOLS YES NO YES
5 TBS_USER_DAT YES
6 TBS_USER_IDX YES
6 rows selected.
SQL>
# View the tablespace file name and size:
SQL> select ts #, round (bytes/1024/1024), name from v $ datafile;
TS # ROUND (BYTES/1024/1024) NAME
------------------------------------------------------------------------------------------------------------
0 8192/opt/oracle/oradata/SOC/system01.dbf
1 16384/opt/oracle/oradata/SOC/rbs01.dbf
2 2048/opt/oracle/oradata/SOC/sysaux01.dbf
4 1024/opt/oracle/oradata/SOC/tools01.dbf
5 51200/opt/oracle/oradata/SOC/udata01.dbf
6 25600/opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
# Modify the data file size:
SQL> alter database datafile '/opt/oracle/oradata/SOC/uindex01.dbf' resize 25610 M;
Database altered.
# View again
SQL> select ts #, round (bytes/1024/1024), name from v $ datafile;
TS # ROUND (BYTES/1024/1024) NAME
------------------------------------------------------------------------------------------------------------
0 8192/opt/oracle/oradata/SOC/system01.dbf
1 16384/opt/oracle/oradata/SOC/rbs01.dbf
2 2048/opt/oracle/oradata/SOC/sysaux01.dbf
4 1024/opt/oracle/oradata/SOC/tools01.dbf
5 51200/opt/oracle/oradata/SOC/udata01.dbf
6 25610/opt/oracle/oradata/SOC/uindex01.dbf
6 rows selected.
SQL>
# SQL statement for checking the space size
SQL> SELECT V1.TABLESPACE _ NAME TABLESPACENAME,
To_char (ROUND (NVL (V1.SPACE, 0) | '(M)' TOTALTABLESPACE,
To_char (ROUND (NVL (V2.SPACE, 0) | '(M)' FREETABLESPACE
FROM (SELECT TABLESPACE_NAME, SUM (BYTES)/1024/1024 space from DBA_DATA_FILES group by TABLESPACE_NAME) V1,
(SELECT TABLESPACE_NAME, SUM (BYTES)/1024/1024 space from DBA_FREE_SPACE group by TABLESPACE_NAME) V2
WHERE V1.TABLESPACE _ NAME = V2.TABLESPACE _ NAME ()
Order by V1.TABLESPACE _ NAME;
2 3 4 5 6 7
TABLESPACENAME TOTALTABLESPACE FREETABLESPACE
------------------------------------------------------------------------------------------------------------
SYSAUX 2048 (M) 1753 (M)
SYSTEM 8192 (M) 7321 (M)
TBS_USER_DAT 51200 (M) 37238 (M)
TBS_USER_IDX 25610 (M) 21014 (M)
TOOLS 1024 (M) 1024 (M)
UNDO 16384 (M) 15467 (M)
6 rows selected.
SQL>



Author scrit