本文介绍了Oracle数据库中表空间的基本操作，包括创建、查询及删除表空间等，并展示了如何使用SQL命令来查看不同类型的表空间及其状态。同时，还演示了如何通过SQL语句更新表中的数据并提交更改。
/* 2008/06/1 4 星期六
＊蒙昭良
*环境：linux5 + Oracle10gR2
*oracle基础练习7－10章
＊存储结构、回滚段、管理表
*/
SQL> select tablespace_name,block_size,status,contents
2 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
SYSTEM 8192 ONLINE PERMANENT
UNDOTBS1 8192 ONLINE UNDO
SYSAUX 8192 ONLINE PERMANENT
TEMP 8192 ONLINE TEMPORARY
USERS 8192 ONLINE PERMANENT
UNDOTBS2 8192 ONLINE UNDO
EXAMPLE 8192 ONLINE PERMANENT
PERFSTAT 8192 ONLINE PERMANENT
RISENET 8192 ONLINE PERMANENT
PIONEER_DATA 8192 ONLINE PERMANENT
PIONEER_INDX 8192 ONLINE PERMANENT
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
PIONEER_UNDO 8192 ONLINE UNDO
PIONEER_TEMP 8192 ONLINE TEMPORARY
13 rows selected.
SQL> create tablespace jinlian
2 datafile '/u01/disk1/jinlian.dbf'
3 size 10M
4 extent management local
5 uniform. size 1M
6 segment space management auto;
Tablespace created.
SQL> select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces
2 where tablespace_name like 'JIN%';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
JINLIAN 8192 LOCAL AUTO
SQL> drop tablespace jinlian;
Tablespace dropped.
SQL> desc dba_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select * from dba_extents where rownum<3;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
SYS
CON$
TABLE SYSTEM
0 1 169 65536 8 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
SYS
CON$
TABLE SYSTEM
1 1 26713 65536 8 1
SQL> desc dba_segments;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
SQL> select owner,segment_name from dba_segments where rownum<3;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYS
CON$
SYS
UNDO$
SQL> desc dba_free_space;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select tablespace_name,bytes/1024/1024 MB from dba_free_space
2 where rownum<3;
TABLESPACE_NAME MB
------------------------------ ----------
SYSTEM .125
SYSTEM 2.9375
SQL> connect scott/mzl
Connected.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
SQL> create table emp_tran
2 as
3 select * from emp;
Table created.
SQL> select empno,ename,job,sal
2 from emp_tran
3 where job='CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
SQL> update emp_tran set sal=2000 where job='CLERK';
4 rows updated.
SQL> select empno,ename,job,sal
2 from emp_tran
3 where job='CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 2000
7876 ADAMS CLERK 2000
7900 JAMES CLERK 2000
7934 MILLER CLERK 2000
SQL> commit;
Commit complete.
SQL> conn sys/mzl as sysdba
Connected.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SQL> select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO
EXAMPLE ONLINE PERMANENT
PERFSTAT ONLINE PERMANENT
RISENET ONLINE PERMANENT
PIONEER_DATA ONLINE PERMANENT
PIONEER_INDX ONLINE PERMANENT
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
PIONEER_UNDO ONLINE UNDO
PIONEER_TEMP ONLINE TEMPORARY
13 rows selected.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> select name,value from v$parameter
2 where name like '%UNDO%';
no rows selected
SQL> select name,value from v$parameter
2 where name like '%undo%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
undo_management
AUTO
undo_tablespace
UNDOTBS1
undo_retention
900
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace jinlian_undo
2 datafile '/u01/disk2/jinlian_undo.dbf'
3 size 5M;
Tablespace created.
SQL> 1
1* select tablespace_name,status,contents from dba_tablespace
SQL> c /dba_tablespace/dba_tablespaces
1* select tablespace_name,status,contents from dba_tablespaces
SQL> l
1 select tablespace_name,status,contents from dba_tablespaces
2* where contents='UNDO'
SQL> /
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
UNDOTBS1 ONLINE UNDO
UNDOTBS2 ONLINE UNDO
JINLIAN_UNDO ONLINE UNDO
PIONEER_UNDO ONLINE UNDO
SQL> set line 120
SQL> col file_name for a40
SQL> col tablespace_name for a15
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB
2 from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME MB
---------- ---------------------------------------- --------------- ----------
7 /u01/app/oracle/oradata/orcl/risenet.dbf RISENET
6 /u01/app/oracle/oradata/orcl/perfstat.db PERFSTAT 500
f
5 /u01/app/oracle/oradata/orcl/example01.d EXAMPLE 100
bf
&nb
＊蒙昭良
*环境：linux5 + Oracle10gR2
*oracle基础练习7－10章
＊存储结构、回滚段、管理表
*/
SQL> select tablespace_name,block_size,status,contents
2 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
SYSTEM 8192 ONLINE PERMANENT
UNDOTBS1 8192 ONLINE UNDO
SYSAUX 8192 ONLINE PERMANENT
TEMP 8192 ONLINE TEMPORARY
USERS 8192 ONLINE PERMANENT
UNDOTBS2 8192 ONLINE UNDO
EXAMPLE 8192 ONLINE PERMANENT
PERFSTAT 8192 ONLINE PERMANENT
RISENET 8192 ONLINE PERMANENT
PIONEER_DATA 8192 ONLINE PERMANENT
PIONEER_INDX 8192 ONLINE PERMANENT
TABLESPACE_NAME BLOCK_SIZE STATUS CONTENTS
------------------------------ ---------- --------- ---------
PIONEER_UNDO 8192 ONLINE UNDO
PIONEER_TEMP 8192 ONLINE TEMPORARY
13 rows selected.
SQL> create tablespace jinlian
2 datafile '/u01/disk1/jinlian.dbf'
3 size 10M
4 extent management local
5 uniform. size 1M
6 segment space management auto;
Tablespace created.
SQL> select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces
2 where tablespace_name like 'JIN%';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
JINLIAN 8192 LOCAL AUTO
SQL> drop tablespace jinlian;
Tablespace dropped.
SQL> desc dba_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select * from dba_extents where rownum<3;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
SYS
CON$
TABLE SYSTEM
0 1 169 65536 8 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
SYS
CON$
TABLE SYSTEM
1 1 26713 65536 8 1
SQL> desc dba_segments;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
SQL> select owner,segment_name from dba_segments where rownum<3;
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SYS
CON$
SYS
UNDO$
SQL> desc dba_free_space;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
SQL> select tablespace_name,bytes/1024/1024 MB from dba_free_space
2 where rownum<3;
TABLESPACE_NAME MB
------------------------------ ----------
SYSTEM .125
SYSTEM 2.9375
SQL> connect scott/mzl
Connected.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT USERS
EMP USERS
BONUS USERS
SALGRADE USERS
SQL> create table emp_tran
2 as
3 select * from emp;
Table created.
SQL> select empno,ename,job,sal
2 from emp_tran
3 where job='CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300
SQL> update emp_tran set sal=2000 where job='CLERK';
4 rows updated.
SQL> select empno,ename,job,sal
2 from emp_tran
3 where job='CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 2000
7876 ADAMS CLERK 2000
7900 JAMES CLERK 2000
7934 MILLER CLERK 2000
SQL> commit;
Commit complete.
SQL> conn sys/mzl as sysdba
Connected.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
SQL> select tablespace_name,status,contents from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
UNDOTBS2 ONLINE UNDO
EXAMPLE ONLINE PERMANENT
PERFSTAT ONLINE PERMANENT
RISENET ONLINE PERMANENT
PIONEER_DATA ONLINE PERMANENT
PIONEER_INDX ONLINE PERMANENT
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
PIONEER_UNDO ONLINE UNDO
PIONEER_TEMP ONLINE TEMPORARY
13 rows selected.
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> select name,value from v$parameter
2 where name like '%UNDO%';
no rows selected
SQL> select name,value from v$parameter
2 where name like '%undo%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
undo_management
AUTO
undo_tablespace
UNDOTBS1
undo_retention
900
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace jinlian_undo
2 datafile '/u01/disk2/jinlian_undo.dbf'
3 size 5M;
Tablespace created.
SQL> 1
1* select tablespace_name,status,contents from dba_tablespace
SQL> c /dba_tablespace/dba_tablespaces
1* select tablespace_name,status,contents from dba_tablespaces
SQL> l
1 select tablespace_name,status,contents from dba_tablespaces
2* where contents='UNDO'
SQL> /
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
UNDOTBS1 ONLINE UNDO
UNDOTBS2 ONLINE UNDO
JINLIAN_UNDO ONLINE UNDO
PIONEER_UNDO ONLINE UNDO
SQL> set line 120
SQL> col file_name for a40
SQL> col tablespace_name for a15
SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 MB
2 from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME MB
---------- ---------------------------------------- --------------- ----------
7 /u01/app/oracle/oradata/orcl/risenet.dbf RISENET
6 /u01/app/oracle/oradata/orcl/perfstat.db PERFSTAT 500
f
5 /u01/app/oracle/oradata/orcl/example01.d EXAMPLE 100
bf
&nb
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12778571/viewspace-346811/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-346811/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
