TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
3 TEMP NO NO YES
4 MYTBS YES NO YES
5 MSP YES NO YES
SQL> select tablespace_name,contents,extent_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM PERMANENT LOCAL
SYSAUX PERMANENT LOCAL
UNDOTBS1 UNDO LOCAL
TEMP TEMPORARY LOCAL
MYTBS PERMANENT LOCAL
MSP PERMANENT LOCAL
SQL> select tablespace_name,contents,extent_management from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM PERMANENT LOCAL
SYSAUX PERMANENT LOCAL
UNDOTBS1 UNDO LOCAL
TEMP TEMPORARY LOCAL
MYTBS PERMANENT LOCAL
MSP PERMANENT LOCAL
MYTEMP01 TEMPORARY LOCAL
7 rows selected.
SQL> alter database default temporary tablespace mytemp01;
Database altered.
SQL> desc database_properties;
Name Null? Type
----------------------------------------- -------- ----------------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION VARCHAR2(4000)
SQL> col property_name format a20
SQL> col PROPERTY_VALUE format a20
SQL> col DESCRIPTION format a20
SQL> select * from database_properties;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
-------------------- -------------------- --------------------
DICT.BASE 2 dictionary base tabl
es version #
DEFAULT_TEMP_TABLESP MYTEMP01 Name of default temp
ACE orary tablespace
SQL> create user msp identified by msp default tablespace fuzhou; #新建用户用于该表空间fuzhou
User created.
SQL> grant connect,resource to msp; #将该用户进行授权
Grant succeeded.
[oracle@server01 ~]$ sqlplus msp/msp
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 28 18:42:26 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t (id integer,name char(10)); #创建一个表
Table created.
SQL> insert into t values(0,'fujian'); #对表插入数据
1 row created.
SQL> commit; #提交
Commit complete.
SQL> select * from t;
ID NAME
---------- ----------
0 fujian
SQL> insert into t values(1,'2222');
1 row created.
SQL> commit;
Commit complete.
[oracle@server01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 28 18:45:15 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace fuzhou read only; （当上文红色的commit未执行提交时候，执行alter指令会hang住）
Tablespace altered.
SQL> conn msp/msp
Connected.
SQL> insert into t values(2,'8888');
insert into t values(2,'8888')
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
SQL> select * from t;
ID NAME
---------- ----------
0 fujian
1 2222
SQL> delete from t where id=1;
delete from t where id=1
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
==============================================================
SQL> conn msp/msp
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
解决方法：
SQL> conn system/123456
Connected.
SQL> @?/sqlplus/admin/pupbld.sql;
SQL> select * from tt;
select * from tt
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/database/msp/fuzhou.dbf'
SQL> alter tablespace fuzhou add datafile
2 '/database/msp/fuzhou02.dbf' size 20M
3 autoextend on next 10M maxsize 100M;
Tablespace altered.
SQL>
SQL>
SQL>
SQL> desc dba_data_files
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
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
ONLINE_STATUS VARCHAR2(7)
SQL> col file_name format a20
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/system SYSTEM NO
01.dbf
/database/msp/sysaux SYSAUX NO
01.dbf
/database/msp/undotb UNDOTBS1 NO
s01.dbf
/database/msp/mspmyt MYTBS NO
bs.dbf
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/msp.db MSP NO
f
/database/msp/fuzhou FUZHOU NO
.dbf
/database/msp/fuzhou FUZHOU YES
02.dbf
7 rows selected.
SQL> alter database datafile
2 '/database/msp/fuzhou.dbf'
3 autoextend on next 10M maxsize 100M;
Database altered.
####也可以将原始的表空间设置为可自动扩展的#########################
SQL>
SQL>
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/system SYSTEM NO
01.dbf
/database/msp/sysaux SYSAUX NO
01.dbf
/database/msp/undotb UNDOTBS1 NO
s01.dbf
/database/msp/mspmyt MYTBS NO
bs.dbf
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/database/msp/msp.db MSP NO
f
/database/msp/fuzhou FUZHOU YES
.dbf
/database/msp/fuzhou FUZHOU YES
02.dbf
7 rows selected.
=================================================================
用resize进行扩展或缩小表空间大小：
注意缩小的时候，不能小于当前表空间使用的大小
SQL> alter database datafile '/database/msp/fuzhou.dbf' resize 50M;
Database altered.
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
? 不能删除下列表空间：
–SYSTEM表空间
–具有活动段的表空间
? INCLUDING CONTENTS将删除段
? INCLUDING CONTENTS AND DATAFILES将删除
数据文件
? CASCADE CONSTRAINTS将删除所有引用完整性约束
DROP TABLESPACE userdata
INCLUDING CONTENTS AND DATAFILES; #########该指令谨慎使用
---------------------------------------------------------------------------------------------
SQL> drop tablespace fuzhou including contents and datafiles;
Tablespace dropped.
SQL> select file_name,tablespace_name,bytes from dba_data_files;
FILE_NAME TABLESPACE_NAME BYTES
-------------------- -------------------- ----------
/database/msp/system SYSTEM 314572800
01.dbf
