Oracle中ORA-01653解决方法

ck1987

　　1.创建一个表空间
　　SQL> create tablespace INVENTORY
　　2  datafile '/u01/app/oracle/oradata/orcl1/inventory01.dbf'
　　3  size 5M autoextend off
　　4  extent management local autoallocate
　　5  segment space management auto
　　6  logging;
　　Tablespace created.
　　2.执和脚本进行测试
　　SQL> @/home/oracle/labs/lab_01_05_02.sql
　　SQL> create table x (a char(1000)) tablespace inventory
　　2  /
　　Table created.
　　SQL> insert into x values('a')
　　2  /
　　1 row created.
　　SQL> insert into x select * from x
　　2  /
　　1 row created.
　　SQL> insert into x select * from x
　　2  /
　　2 rows created.
　　SQL> insert into x select * from x
　　2  /
　　4 rows created.
　　SQL> insert into x select * from x
　　2  /
　　8 rows created.
　　SQL> insert into x select * from x
　　2  /
　　16 rows created.
　　SQL> insert into x select * from x
　　2  /
　　32 rows created.
　　SQL> insert into x select * from x
　　2  /
　　64 rows created.
　　SQL> insert into x select * from x
　　2  /
　　128 rows created.
　　SQL> insert into x select * from x
　　2  /
　　256 rows created.
　　SQL> insert into x select * from x
　　2  /
　　512 rows created.
　　SQL> insert into x select * from x
　　2  /
　　1024 rows created.
　　SQL> insert into x select * from x
　　2  /
　　insert into x select * from x
　　*
　　ERROR at line 1:
　　ORA-01653: unable to extend table SYS.X by 128 in tablespace INVENTORY
　　SQL> commit
　　2  /
　　Commit complete.
　　SQL> quit
　　Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
　　With the Partitioning, OLAP, Data Mining and Real Application Testing options
　　3.修改表空间大小
　　SQL> alter database datafile
　　2  '/u01/app/oracle/oradata/orcl1/inventory01.dbf'
　　3  resize 50M;
　　Database altered.
　　4.删除表
　　SQL> drop table x;
　　Table dropped.
　　5.再次进行测试
　　SQL> @/home/oracle/labs/lab_01_05_02.sql
　　SQL> create table x (a char(1000)) tablespace inventory
　　2  /
　　Table created.
　　SQL> insert into x values('a')
　　2  /
　　1 row created.
　　SQL> insert into x select * from x
　　2  /
　　1 row created.
　　SQL> insert into x select * from x
　　2  /
　　2 rows created.
　　SQL> insert into x select * from x
　　2  /
　　4 rows created.
　　SQL> insert into x select * from x
　　2  /
　　8 rows created.
　　SQL> insert into x select * from x
　　2  /
　　16 rows created.
　　SQL> insert into x select * from x
　　2  /
　　32 rows created.
　　SQL> insert into x select * from x
　　2  /
　　64 rows created.
　　SQL> insert into x select * from x
　　2  /
　　128 rows created.
　　SQL> insert into x select * from x
　　2  /
　　256 rows created.
　　SQL> insert into x select * from x
　　2  /
　　512 rows created.
　　SQL> insert into x select * from x
　　2  /
　　1024 rows created.
　　SQL> insert into x select * from x
　　2  /
　　2048 rows created.
　　SQL> commit
　　2  /
　　Commit complete.
　　SQL> quit
　　Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
　　With the Partitioning, OLAP, Data Mining and Real Application Testing options6
　　6.脚本文件内容如下
　　[oracle@db1 labs]$ cat lab_01_05_02.sql
　　set echo off
　　-- Oracle Database 10g: Administration Workshop I
　　-- Oracle Server Technologies - Curriculum Development
　　--
　　-- ***Training purposes only***
　　-- ***Not appropriate for production use***
　　--
　　set echo on
　　create table x (a char(1000)) tablespace inventory
　　/
　　insert into x values('a')
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　insert into x select * from x
　　/
　　commit
　　/
　　quit