oracle10g_create tablespace_測試_2
SQL> alter system set db_cache_size=10m scope=spfile;---為了建立非標準塊大小的表空間,必配置的引數
System altered.
SQL> alter system set db_2k_cache_size=5m scope=spfile;--同上
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 96471076 bytes
Database Buffers 213909504 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
SQL> show parameter db_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 8M
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
db_cache_advice string ON
db_cache_size big integer 12M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
db_domain string
db_file_multiblock_read_count integer 16
db_file_name_convert string
db_files integer 200
db_flashback_retention_target integer 1440
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 0
db_name string ora10g
db_recovery_file_dest string /oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
db_unique_name string ora10g
db_writer_processes integer 1
dbwr_io_slaves integer 0
rdbms_server_dn string
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m blocksize 2k;--顯示指定非標準塊大小
Tablespace created.
Restriction on BLOCKSIZE
---You cannot specify nonstandard block sizes for a temporary tablespace
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,block_size from dba_tablespaces where tablespace_name='TEST';
TABLESPACE BLOCK_SIZE
---------- ----------
TEST 2048
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE
----------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
FS1
TRANS
TEST
SMALLTBS
MINEXTENT
10 rows selected.
SQL> alter tablespace temp offline;--不能對臨時表空間下線
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> alter tablespace test offline;
Tablespace altered.
SQL> alter tablespace undotbs1 offline;--不能對撤消表空間下線
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
---下列開始測試構建表空間的相關屬性:size就是初始分給此表空間的大小,
--next 就是下次擴充套件分配的大小,maxsize就是這個表空間一直擴充套件這個大小就到頭了
--reuse就是重用建立這個表空間的資料檔案(如果它存在)
--autoextend on 就是開啟自動空間分配,你也可以autoextend off
SQL> create tablespace tbs_02 datafile 'tbs_f5.dbf' size 500k reuse autoextend on next 500k maxsize 100m;
Tablespace created.
SQL> create user tbs_02 identified by system account unlock default tablespace tbs_02;--為了配合測試構建一個對應的使用者
User created.
SQL> grant resource,connect to tbs_02;
Grant succeeded.
SQL> set long 99999
SQL> select dbms_metadata.get_ddl('TABLESPACE','TBS_02') from dual;---檢視建表空間的ddl
DBMS_METADATA.GET_DDL('TABLESPACE','TBS_02')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TBS_02" DATAFILE
'/oracle/product/10.2.0/db_1/dbs/tbs_f5.dbf' SIZE 516096
AUTOEXTEND ON NEXT 516096 MAXSIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> conn tbs_02/system
Connected.
SQL> insert into tab1 values(1);--利用insert開始插入資料,測試對比這個表空間最多可以到多大
1 row created.
SQL> commit;
Commit complete.
SQL> insert into tab1 select * from tab1;
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> commit;
Commit complete.
SQL> desc user_segments;
Name Null? Type
----------------------------------------- -------- ----------------------------
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
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
BUFFER_POOL VARCHAR2(7)
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';--檢視此表當前所佔的大小
SEGMENT_NAME
--------------------------------------------------------------------------------
MB
----------
TAB1
2
SQL> col segment_name for a10
SQL> /
SEGMENT_NA MB
---------- ----------
TAB1 2
SQL> insert into tab1 select * from tab1;
131072 rows created.
SQL> /
262144 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';
SEGMENT_NA MB
---------- ----------
TAB1 7
SQL> insert into tab1 select * from tab1;
524288 rows created.
SQL> /
1048576 rows created.
SQL> /
2097152 rows created.
SQL> commit;
Commit complete.
SQL> insert into tab1 select * from tab1;---大家發現了沒有,差不多到maxsize哪個大小了,所以不能再擴充套件了和插入資料了
insert into tab1 select * from tab1
*
ERROR at line 1:
ORA-01653: unable to extend table TBS_02.TAB1 by 1024 in tablespace TBS_02
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';
SEGMENT_NA MB
---------- ----------
TAB1 96
System altered.
SQL> alter system set db_2k_cache_size=5m scope=spfile;--同上
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279964 bytes
Variable Size 96471076 bytes
Database Buffers 213909504 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
SQL> show parameter db_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 8M
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TRUE
db_block_size integer 8192
db_cache_advice string ON
db_cache_size big integer 12M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
db_domain string
db_file_multiblock_read_count integer 16
db_file_name_convert string
db_files integer 200
db_flashback_retention_target integer 1440
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 0
db_name string ora10g
db_recovery_file_dest string /oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
db_recycle_cache_size big integer 0
db_unique_name string ora10g
db_writer_processes integer 1
dbwr_io_slaves integer 0
rdbms_server_dn string
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> create tablespace test datafile '/oracle/db/test01.dbf' size 10m blocksize 2k;--顯示指定非標準塊大小
Tablespace created.
Restriction on BLOCKSIZE
---You cannot specify nonstandard block sizes for a temporary tablespace
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,block_size from dba_tablespaces where tablespace_name='TEST';
TABLESPACE BLOCK_SIZE
---------- ----------
TEST 2048
SQL>
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE
----------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
FS1
TRANS
TEST
SMALLTBS
MINEXTENT
10 rows selected.
SQL> alter tablespace temp offline;--不能對臨時表空間下線
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
SQL> alter tablespace test offline;
Tablespace altered.
SQL> alter tablespace undotbs1 offline;--不能對撤消表空間下線
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
---下列開始測試構建表空間的相關屬性:size就是初始分給此表空間的大小,
--next 就是下次擴充套件分配的大小,maxsize就是這個表空間一直擴充套件這個大小就到頭了
--reuse就是重用建立這個表空間的資料檔案(如果它存在)
--autoextend on 就是開啟自動空間分配,你也可以autoextend off
SQL> create tablespace tbs_02 datafile 'tbs_f5.dbf' size 500k reuse autoextend on next 500k maxsize 100m;
Tablespace created.
SQL> create user tbs_02 identified by system account unlock default tablespace tbs_02;--為了配合測試構建一個對應的使用者
User created.
SQL> grant resource,connect to tbs_02;
Grant succeeded.
SQL> set long 99999
SQL> select dbms_metadata.get_ddl('TABLESPACE','TBS_02') from dual;---檢視建表空間的ddl
DBMS_METADATA.GET_DDL('TABLESPACE','TBS_02')
--------------------------------------------------------------------------------
CREATE TABLESPACE "TBS_02" DATAFILE
'/oracle/product/10.2.0/db_1/dbs/tbs_f5.dbf' SIZE 516096
AUTOEXTEND ON NEXT 516096 MAXSIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
SQL> conn tbs_02/system
Connected.
SQL> insert into tab1 values(1);--利用insert開始插入資料,測試對比這個表空間最多可以到多大
1 row created.
SQL> commit;
Commit complete.
SQL> insert into tab1 select * from tab1;
1 row created.
SQL> /
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> commit;
Commit complete.
SQL> desc user_segments;
Name Null? Type
----------------------------------------- -------- ----------------------------
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
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
BUFFER_POOL VARCHAR2(7)
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';--檢視此表當前所佔的大小
SEGMENT_NAME
--------------------------------------------------------------------------------
MB
----------
TAB1
2
SQL> col segment_name for a10
SQL> /
SEGMENT_NA MB
---------- ----------
TAB1 2
SQL> insert into tab1 select * from tab1;
131072 rows created.
SQL> /
262144 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';
SEGMENT_NA MB
---------- ----------
TAB1 7
SQL> insert into tab1 select * from tab1;
524288 rows created.
SQL> /
1048576 rows created.
SQL> /
2097152 rows created.
SQL> commit;
Commit complete.
SQL> insert into tab1 select * from tab1;---大家發現了沒有,差不多到maxsize哪個大小了,所以不能再擴充套件了和插入資料了
insert into tab1 select * from tab1
*
ERROR at line 1:
ORA-01653: unable to extend table TBS_02.TAB1 by 1024 in tablespace TBS_02
SQL> select segment_name,bytes/1024/1024 mb from user_segments where segment_name='TAB1';
SEGMENT_NA MB
---------- ----------
TAB1 96
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-614507/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle10g_create tablespace_測試_1Oracle
- 測試2
- 【星雲測試】開發者測試（2）-採用精準測試工具對J2EE Guns開發框架進行測試框架
- 軟體測試理論（2）自動化測試
- sdf 測試-2-openssl
- 開發者測試(2)-採用精準測試工具對J2EE Guns開發框架進行測試框架
- 測試測試測試測試測試測試
- 軟體測試面試題（2）面試題
- struts2 漏洞測試工具
- 軟體需求分析測試2
- 測試你的前端程式碼 - part2（單元測試）前端
- 測試你的前端程式碼 – part2（單元測試）前端
- 利用 Rize 來進行 UI 測試或 E2E 測試UI
- WebApi介面測試工具（2）：WebApiTestClientWebAPIclient
- 二、介面自動化測試（2）
- 軟體測試要學什麼（2）白盒測試詳解教程
- SpringBoot2 整合測試元件，七種測試手段對比Spring Boot元件
- 使用Angular CLI進行單元測試和E2E測試Angular
- Unixbench的使用（綜合效能測試、2D測試）和問題解決
- linux滲透測試技巧2則Linux
- 控制元件測試功能點摘要2控制元件
- Puppeteer E2E測試入門
- 第2章 編寫測試函式函式
- db2 構造測試資料DB2
- db2 load 自增測試DB2
- DB2備份恢復測試DB2
- J2EE 提供的JDBC測試JDBC
- 2024暑假集訓測試2
- App測試、Web測試和介面測試一般測試流程APPWeb
- 介面測試測試流程
- Detectron2 快速開始，使用 WebCam 測試Web
- 滲透測試技巧總結更新篇2
- Oracle 11.2.0.1 Result Cache 測試 - 2 引數Oracle
- 章五帶上眼罩測試軟體(2)
- 程式碼審查“查”什麼(2)：測試
- oracle10g_rman_語法測試_2Oracle
- sdf 測試-2-龍脈智慧鑰匙
- Flutter 學習之路 - 測試（單元測試，Widget 測試，整合測試）Flutter