oracle10g_create tablespace_測試_1
SQL> create user test identified by system account unlock;
User created.
SQL> grant resource,connect to test;
Grant succeeded.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m;
Tablespace created.
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_name,tablespace_name,bytes/1024/1024 mb,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------
TABLESPACE_NAME MB AUT
------------------------------ ---------- ---
/oracle/db/test01.dbf
TEST 10 NO
FILE_NAME TABLESPACE MB AUT
------------------------------ ---------- ---------- ---
/oracle/db/test01.dbf TEST 10 NO
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;---檢視錶空間的定義語句
SQL> set long 99999
SQL> /
DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TEST" DATAFILE
'/oracle/db/test01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> conn test/system --如果一般使用者要建立/刪除表空間,要給許可權create|drop tablespace
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;
create tablespace own datafile '/oracle/db/own01.dbf' size 10m
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create tablespace to test;
Grant succeeded.
SQL> conn test/system
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;
Tablespace created.
SQL> drop tablespace own including contents and datafiles;
drop tablespace own including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant drop tablespace to test;
Grant succeeded.
SQL> conn test/system
Connected.
SQL> drop tablespace own including contents and datafiles;--刪除表空間及其資料檔案
Tablespace dropped.
SQL> conn /as sysdba
Connected.
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)
--檢視錶空間相關屬性:塊大小,是否大檔案(大檔案表空間就是一個表空間只能有一個資料檔案,此檔案大小可達
-- 資料檔案或者檔案為128 terabytes (TB) 對於塊大小為 32K blocks ,對於塊大小為8k資料檔案或臨時檔案大小為32TB
--我們一般的業務不可能用哪麼大,當然預設是smallfile,它最大檔案數為1022
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='TEST';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
TEST 8192 LOGGING SYSTEM NO
SQL> create bigfile tablespace bigtbs datafile '/oracle/db/bigtbs01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='BIGTBS';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
BIGTBS 8192 LOGGING SYSTEM YES
SQL> drop tablespace bigtbs including contents and datafiles;
Tablespace dropped.
SQL> create smallfile tablespace smalltbs datafile '/oracle/db/smalltbs01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='SMALLTBS';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
SMALLTBS 8192 LOGGING SYSTEM NO
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';---檢視錶空間是否線上,下線(就是此表空間可否讀寫)
TABLESPACE STATUS
---------- ---------
TEST ONLINE
SQL> alter tablespace test offline;--此表空間下線
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';
TABLESPACE STATUS
---------- ---------
TEST OFFLINE
SQL> alter tablespace test online;
Tablespace altered.
SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='TEST';
TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
TEST 1 2147483645
SQL> show user
USER is "SYS"
SQL> create tablespace minextent datafile '/oracle/db/minextent01.dbf' size 10m minimum extent 10 nologging offline
2 default compress;
Tablespace created.
SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
MINEXTENT 1 2147483645
---表空間所屬表是否壓縮
SQL> select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE LOGGING STATUS MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT NOLOGGING OFFLINE 65536 ENABLED
SQL> alter tablespace minextent default nocompress;
Tablespace altered.
SQL> select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE LOGGING STATUS MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT NOLOGGING OFFLINE 65536 DISABLED
SQL> alter tablespace minextent online;
Tablespace altered.
SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE EXTENT_MAN SEGMEN
---------- ---------- ------
MINEXTENT LOCAL AUTO
SQL> alter tablespace minextent extent management dictionary;--10g不能用dictionary管理方式了,太多的字典表競爭
alter tablespace minextent extent management dictionary
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management local uniform. size 1m;
Tablespace created.
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m flashback off;--開啟此表空間是否可以閃回,10g新特性
Tablespace created.
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> desc v$tablespace;---閃回列,對應上面
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';
NAME INC BIG FLA
------------------------------ --- --- ---
TEST YES NO NO
SQL> alter tablespace test flashback on;
alter tablespace test flashback on
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 79693860 bytes
Database Buffers 230686720 bytes
Redo Buffers 2912256 bytes
Database mounted.
SQL> alter tablespace test flashback on;--變更表空間為閃回,在database mount狀態下
Tablespace altered.
SQL> alter database open;
Database altered.
SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';
NAME INC BIG FLA
------------------------------ --- --- ---
TEST YES NO YES
User created.
SQL> grant resource,connect to test;
Grant succeeded.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m;
Tablespace created.
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_name,tablespace_name,bytes/1024/1024 mb,autoextensible from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------
TABLESPACE_NAME MB AUT
------------------------------ ---------- ---
/oracle/db/test01.dbf
TEST 10 NO
FILE_NAME TABLESPACE MB AUT
------------------------------ ---------- ---------- ---
/oracle/db/test01.dbf TEST 10 NO
SQL> select dbms_metadata.get_ddl('TABLESPACE','TEST') from dual;---檢視錶空間的定義語句
SQL> set long 99999
SQL> /
DBMS_METADATA.GET_DDL('TABLESPACE','TEST')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TEST" DATAFILE
'/oracle/db/test01.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> conn test/system --如果一般使用者要建立/刪除表空間,要給許可權create|drop tablespace
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;
create tablespace own datafile '/oracle/db/own01.dbf' size 10m
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create tablespace to test;
Grant succeeded.
SQL> conn test/system
Connected.
SQL> create tablespace own datafile '/oracle/db/own01.dbf' size 10m;
Tablespace created.
SQL> drop tablespace own including contents and datafiles;
drop tablespace own including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant drop tablespace to test;
Grant succeeded.
SQL> conn test/system
Connected.
SQL> drop tablespace own including contents and datafiles;--刪除表空間及其資料檔案
Tablespace dropped.
SQL> conn /as sysdba
Connected.
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)
--檢視錶空間相關屬性:塊大小,是否大檔案(大檔案表空間就是一個表空間只能有一個資料檔案,此檔案大小可達
-- 資料檔案或者檔案為128 terabytes (TB) 對於塊大小為 32K blocks ,對於塊大小為8k資料檔案或臨時檔案大小為32TB
--我們一般的業務不可能用哪麼大,當然預設是smallfile,它最大檔案數為1022
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='TEST';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
TEST 8192 LOGGING SYSTEM NO
SQL> create bigfile tablespace bigtbs datafile '/oracle/db/bigtbs01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='BIGTBS';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
BIGTBS 8192 LOGGING SYSTEM YES
SQL> drop tablespace bigtbs including contents and datafiles;
Tablespace dropped.
SQL> create smallfile tablespace smalltbs datafile '/oracle/db/smalltbs01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name,block_size,logging,allocation_type,bigfile from dba_tablespaces where tablespace_name='SMALLTBS';
TABLESPACE BLOCK_SIZE LOGGING ALLOCATIO BIG
---------- ---------- --------- --------- ---
SMALLTBS 8192 LOGGING SYSTEM NO
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';---檢視錶空間是否線上,下線(就是此表空間可否讀寫)
TABLESPACE STATUS
---------- ---------
TEST ONLINE
SQL> alter tablespace test offline;--此表空間下線
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';
TABLESPACE STATUS
---------- ---------
TEST OFFLINE
SQL> alter tablespace test online;
Tablespace altered.
SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='TEST';
TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
TEST 1 2147483645
SQL> show user
USER is "SYS"
SQL> create tablespace minextent datafile '/oracle/db/minextent01.dbf' size 10m minimum extent 10 nologging offline
2 default compress;
Tablespace created.
SQL> select tablespace_name,min_extents,max_extents from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE MIN_EXTENTS MAX_EXTENTS
---------- ----------- -----------
MINEXTENT 1 2147483645
---表空間所屬表是否壓縮
SQL> select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE LOGGING STATUS MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT NOLOGGING OFFLINE 65536 ENABLED
SQL> alter tablespace minextent default nocompress;
Tablespace altered.
SQL> select tablespace_name,logging,status,min_extlen,def_tab_compression from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE LOGGING STATUS MIN_EXTLEN DEF_TAB_
---------- --------- --------- ---------- --------
MINEXTENT NOLOGGING OFFLINE 65536 DISABLED
SQL> alter tablespace minextent online;
Tablespace altered.
SQL> select tablespace_name,extent_management,segment_space_management from dba_tablespaces where tablespace_name='MINEXTENT';
TABLESPACE EXTENT_MAN SEGMEN
---------- ---------- ------
MINEXTENT LOCAL AUTO
SQL> alter tablespace minextent extent management dictionary;--10g不能用dictionary管理方式了,太多的字典表競爭
alter tablespace minextent extent management dictionary
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary segment space management manual
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary;
create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management dictionary
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m extent management local uniform. size 1m;
Tablespace created.
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m flashback off;--開啟此表空間是否可以閃回,10g新特性
Tablespace created.
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> desc v$tablespace;---閃回列,對應上面
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';
NAME INC BIG FLA
------------------------------ --- --- ---
TEST YES NO NO
SQL> alter tablespace test flashback on;
alter tablespace test flashback on
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 79693860 bytes
Database Buffers 230686720 bytes
Redo Buffers 2912256 bytes
Database mounted.
SQL> alter tablespace test flashback on;--變更表空間為閃回,在database mount狀態下
Tablespace altered.
SQL> alter database open;
Database altered.
SQL> select name,included_in_database_backup,bigfile,flashback_on from v$tablespace where name='TEST';
NAME INC BIG FLA
------------------------------ --- --- ---
TEST YES NO YES
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-614492/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g_create tablespace_測試_2Oracle
- 測試1
- 第1章 軟體測試概述線上測試
- 單元測試如何測試私有方法_1
- 『居善地』介面測試 — 1、介面測試的概念
- .Net單元測試xUnit和整合測試指南(1)
- 單元測試理解· 1
- 測試測試測試測試測試測試
- 測試TOM=SQLLOADER1SQL
- 【1】測試基礎知識
- JavaScript 測試教程-part 1：用 Jest 進行單元測試JavaScript
- 軟體測試入門【1】什麼是軟體測試
- 【效能測試】效能測試各知識第1篇：效能測試大綱【附程式碼文件】
- unittest 單元測試框架教程 1-執行測試指令碼框架指令碼
- 軟體測試要學什麼（1）黑盒測試詳解教程
- 前端測試：Part1 （介紹）前端
- 測試功能點總結摘要1
- APP專項測試實戰1APP
- LoadRunner測試結果分析（1）
- Oracle 11.2.0.1 Result Cache 測試 - 1Oracle
- vmware測試1 儲存劃分
- 測試TOM=SQLLDR函式使用1SQL函式
- 1.測試理論知識
- 大話效能測試系列（1）- 效能測試概念與主要指標指標
- 百度測試1，陣列單詞首位相同測試（php）陣列PHP
- 發現深層次的bug——業務測試 1、業務測試簡介
- 效能測試學習(1)-效能測試分類與常見術語
- 百萬資料查詢測試 只需1秒--Sql語句效率測試SQL
- 軟體測試經典面試題（1）面試題
- 1. mysql的安裝與測試MySql
- 24數媒Java上機測試1Java
- 初三奧賽模擬測試1
- 1052 賣個萌(測試點1）
- 2024暑假集訓測試1
- 測試開發：從0到1學習如何測試API閘道器API
- 程式碼重構與單元測試——重構1的單元測試（四）
- MogDB/openGauss 壞塊測試-對啟動的影響-測試筆記1筆記
- 從 0 到 1 開展軟體測試