oracle基礎練習6章 表空間和資料檔案
/* 2008/06/1 星期五
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習6章
＊表空間和資料檔案
*/
SQL> set line 130
SQL> col tablespace_name for a15
SQL> select tablespace_name,block_size,extent_management,segment_space_management
2 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
--------------- ---------- ---------- ------
SYSTEM 8192 LOCAL MANUAL
UNDOTBS1 8192 LOCAL MANUAL
SYSAUX 8192 LOCAL AUTO
TEMP 8192 LOCAL MANUAL
USERS 8192 LOCAL AUTO
UNDOTBS2 8192 LOCAL MANUAL
EXAMPLE 8192 LOCAL AUTO
PERFSTAT 8192 LOCAL AUTO
RISENET 8192 LOCAL AUTO
9 rows selected.
SQL> select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,min_extlen from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
--------------- -------------- ----------- ----------- ------------ ----------
SYSTEM 65536 2147483645 65536
UNDOTBS1 65536 2147483645 65536
SYSAUX 65536 2147483645 65536
TEMP 1048576 1048576 0 1048576
USERS 65536 2147483645 65536
UNDOTBS2 65536 2147483645 65536
EXAMPLE 65536 2147483645 65536
PERFSTAT 65536 2147483645 65536
RISENET 65536 2147483645 65536
9 rows selected.
SQL> create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5 default storage (initial 50K next 50K maxextents 100 pctincrease 0);
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5* default storage (initial 50K next 50K maxextents 100 pctincrease 0)
SQL> del 5
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4* minimum extent 50k extent management dictionary
SQL> /
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> del 4
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3* '/u01/disk4/jinlian02.dbf' size 20M
SQL> a management dictionary;
3* '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3* '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> /
'/u01/disk4/jinlian02.dbf' size 20M management dictionary
*
ERROR at line 3:
ORA-02180: invalid option for CREATE TABLESPACE
SQL> select file_id,file_name,tablespace_name from db_data_files order by file_id;
select file_id,file_name,tablespace_name from db_data_files order by file_id
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> desc db_data_files;
ERROR:
ORA-04043: object db_data_files does not exist
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> select file_id,file_name,tablespace_name from dba_datafile_files order by file_id;
select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> l
1* select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
SQL> c /datafile/data
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> l
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> /
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
1
/u01/app/oracle/oradata/orcl/system01.dbf
SYSTEM
2
/u01/app/oracle/oradata/orcl/undotbs01.dbf
UNDOTBS1
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
3
/u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSAUX
4
/u01/app/oracle/oradata/orcl/users01.dbf
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
USERS
5
/u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE
6
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
/u01/app/oracle/oradata/orcl/perfstat.dbf
PERFSTAT
7
/u01/app/oracle/oradata/orcl/risenet.dbf
RISENET
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
8
/u01/app/oracle/oradata/orcl/undotbs02.dbf
UNDOTBS2
＊蒙昭良
*環境：linux5 + Oracle10gR2
*oracle基礎練習6章
＊表空間和資料檔案
*/
SQL> set line 130
SQL> col tablespace_name for a15
SQL> select tablespace_name,block_size,extent_management,segment_space_management
2 from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
--------------- ---------- ---------- ------
SYSTEM 8192 LOCAL MANUAL
UNDOTBS1 8192 LOCAL MANUAL
SYSAUX 8192 LOCAL AUTO
TEMP 8192 LOCAL MANUAL
USERS 8192 LOCAL AUTO
UNDOTBS2 8192 LOCAL MANUAL
EXAMPLE 8192 LOCAL AUTO
PERFSTAT 8192 LOCAL AUTO
RISENET 8192 LOCAL AUTO
9 rows selected.
SQL> select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,min_extlen from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE MIN_EXTLEN
--------------- -------------- ----------- ----------- ------------ ----------
SYSTEM 65536 2147483645 65536
UNDOTBS1 65536 2147483645 65536
SYSAUX 65536 2147483645 65536
TEMP 1048576 1048576 0 1048576
USERS 65536 2147483645 65536
UNDOTBS2 65536 2147483645 65536
EXAMPLE 65536 2147483645 65536
PERFSTAT 65536 2147483645 65536
RISENET 65536 2147483645 65536
9 rows selected.
SQL> create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5 default storage (initial 50K next 50K maxextents 100 pctincrease 0);
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4 minimum extent 50k extent management dictionary
5* default storage (initial 50K next 50K maxextents 100 pctincrease 0)
SQL> del 5
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3 '/u01/disk4/jinlian02.dbf' size 20M
4* minimum extent 50k extent management dictionary
SQL> /
create tablespace jinlian
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> del 4
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3* '/u01/disk4/jinlian02.dbf' size 20M
SQL> a management dictionary;
3* '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> l
1 create tablespace jinlian
2 datafile '/u01/disk2/jinlian01.dbf' size 10M,
3* '/u01/disk4/jinlian02.dbf' size 20M management dictionary
SQL> /
'/u01/disk4/jinlian02.dbf' size 20M management dictionary
*
ERROR at line 3:
ORA-02180: invalid option for CREATE TABLESPACE
SQL> select file_id,file_name,tablespace_name from db_data_files order by file_id;
select file_id,file_name,tablespace_name from db_data_files order by file_id
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> desc db_data_files;
ERROR:
ORA-04043: object db_data_files does not exist
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> select file_id,file_name,tablespace_name from dba_datafile_files order by file_id;
select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> l
1* select file_id,file_name,tablespace_name from dba_datafile_files order by file_id
SQL> c /datafile/data
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> l
1* select file_id,file_name,tablespace_name from dba_data_files order by file_id
SQL> /
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
1
/u01/app/oracle/oradata/orcl/system01.dbf
SYSTEM
2
/u01/app/oracle/oradata/orcl/undotbs01.dbf
UNDOTBS1
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
3
/u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSAUX
4
/u01/app/oracle/oradata/orcl/users01.dbf
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
USERS
5
/u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE
6
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
/u01/app/oracle/oradata/orcl/perfstat.dbf
PERFSTAT
7
/u01/app/oracle/oradata/orcl/risenet.dbf
RISENET
FILE_ID
----------
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
---------------
8
/u01/app/oracle/oradata/orcl/undotbs02.dbf
UNDOTBS2
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12778571/viewspace-344628/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle基礎管理——表空間和資料檔案Oracle
- 基礎知識5——表空間和資料檔案
- Oracle 表空間與資料檔案Oracle
- oracle 資料檔案表空間管理Oracle
- 表空間和資料檔案管理
- 【學習日記】oracle之表空間、資料檔案、控制檔案Oracle
- oracle 表空間 資料檔案 筆記Oracle筆記
- Oracle 表空間資料檔案遷移Oracle
- 表空間&資料檔案和控制檔案(zt)
- 表空間和資料檔案的管理
- oracle 普通表空間資料檔案壞塊Oracle
- oracle 回收表空間的資料檔案大小Oracle
- 誤刪oracle資料庫表空間檔案Oracle資料庫
- 重新命名資料檔案和表空間
- 檢視Oracle資料庫表空間大小，是否需要增加表空間的資料檔案Oracle資料庫
- 移動資料檔案、系統表空間檔案、臨時表空間檔案
- Oracle資料庫表空間的資料檔案大小上限。Oracle資料庫
- ORACLE表空間、資料檔案離線問題Oracle
- (個人)Oracle 表空間資料檔案遷移（轉）Oracle
- oracle誤刪除表空間的資料檔案Oracle
- Oracle案例11——Oracle表空間資料庫檔案收縮Oracle資料庫
- Oracle 表空間增加檔案Oracle
- Oracle基礎 01 表空間 tablespaceOracle
- MySQL innodb共享表空間新增表空間資料檔案方法MySql
- 表空間中有資料也可以壓縮表空間（資料檔案）大小
- Oracle 基礎溫習1 建立表空間和新使用者Oracle
- oracle rac on aix 下為表空間增加資料檔案OracleAI
- oracle刪除(釋放)資料檔案/表空間流程Oracle
- 如何檢視Oracle資料庫表空間大小(空閒、已使用),是否要增加表空間的資料檔案...Oracle資料庫
- 9.管理表空間和資料檔案(筆記)筆記
- 刪除表空間和表空間包含的檔案
- 資料檔案,表空間的移動
- 表空間資料檔案故障處理
- 線上遷移表空間資料檔案
- 表空間新增資料檔案的疑惑
- Oracle基礎結構之表空間Oracle
- oracle 小議如何從表空間 刪除 資料檔案Oracle
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle