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