oracle基礎練習7－10章 儲存結構、回滾段、管理表
/* 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-關於回滾段Oracle資料庫
- ORACLE回滾段管理Oracle
- oracle回滾段 undo 表空間Oracle
- Oracle基礎 03 回滾表空間 undoOracle
- ORACLE回滾段Oracle
- oracle基礎練習1－4章Oracle
- ORACLE回滾段(1)Oracle
- ORACLE回滾段(2)Oracle
- ORACLE回滾段(轉)Oracle
- Oracle學習總結--基礎部分（儲存與索引）Oracle索引
- oracle基礎練習5章 重做日誌Oracle
- oracle基礎練習6章 表空間和資料檔案Oracle
- ORACLE 回滾段詳解Oracle
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：修改還原表空間到固定大小Oracle資料庫
- Oracle的回滾段介紹Oracle
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：檢視還原資訊Oracle資料庫
- oracle 基礎溫習之 儲存過程Oracle儲存過程
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：檢視控制檔案資訊Oracle資料庫
- 《MySQL 基礎篇》十一：索引的儲存結構MySql索引
- 回滾操作、回滾段的理解
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-關於其它儲存結構Oracle資料庫
- ORACLE技術專題-- 回滾段Oracle
- Oracle基礎結構之表空間Oracle
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：檢視錶空間的資訊Oracle資料庫
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-關於資料庫儲存結構Oracle資料庫
- SQL code----檢視回滾段名稱及大小 回滾段的管理SQL
- 【UNDO】Oracle系統回滾段說明Oracle
- oracle物理儲存結構理解Oracle
- Oracle資料儲存結構Oracle
- innodb表空間儲存結構
- Oracle 基礎 ----procedure(儲存過程)Oracle儲存過程
- 鎖表時KILL SESSION及回滾段監控(回滾時間評估)Session
- 管理資料庫儲存結構資料庫
- JDBC 事務（二）回滾到儲存點JDBC
- oracle 11g 回滾段的測試Oracle
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：檢視歸檔重做日誌檔案資訊Oracle資料庫
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-練習：檢視聯機重做日誌檔案資訊Oracle資料庫
- 2 Day DBA-管理Oracle例項-管理資料庫儲存結構-關於表空間Oracle資料庫