Oracle 数据库对象管理：表空间与表的操作

这次我们将深入学习如何管理数据库对象的核心——表空间 (Tablespace) 和 表 (Table)。理解如何创建、修改和删除这些对象，是进行有效数据存储和管理的基础。

基本语法格式：

CREATE TABLESPACE tablespace_name
DATAFILE 'datafile_path_and_name' SIZE initial_size
[AUTOEXTEND ON [NEXT increment_size [MAXSIZE max_size | UNLIMITED]]]
[EXTENT MANAGEMENT LOCAL [AUTOALLOCATE | UNIFORM SIZE uniform_extent_size]]
[SEGMENT SPACE MANAGEMENT AUTO | MANUAL];

代码案例：创建一个名为 app_data_ts 的表空间

CREATE TABLESPACE app_data_ts
DATAFILE '/u01/app/oracle/oradata/ORCL/app_data01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

(路径 /u01/app/oracle/oradata/ORCL/ 请根据实际环境替换)

基本语法格式：

CREATE TABLE [schema_name.]table_name (
column_name1 datatype [DEFAULT default_value] [column_constraint1 ...],
column_name2 datatype [DEFAULT default_value] [column_constraint2 ...],
...
[table_constraint1, ...]
)
[TABLESPACE tablespace_name]
[storage_clauses ...];

代码案例：创建一个 products 表

CREATE TABLE products (
product_id NUMBER(10) CONSTRAINT pk_product PRIMARY KEY,
product_name VARCHAR2(100) CONSTRAINT nn_product_name NOT NULL,
category_id NUMBER(5),
unit_price NUMBER(8,2) CONSTRAINT ck_product_price CHECK (unit_price >= 0),
supplier_id NUMBER(10),
entry_date DATE DEFAULT SYSDATE,
CONSTRAINT uq_product_name UNIQUE (product_name)
)
TABLESPACE app_data_ts;

1. 创建表空间：编写SQL语句创建一个名为 user_data_ts 的表空间，数据文件为 /u02/oradata/DBNAME/user_data01.dbf (替换DBNAME)，初始大小50M，允许自动扩展，每次10M，最大200M。区管理本地自动分配，段空间自动管理。
2. 创建用户表：在 user_data_ts 表空间中创建一张名为 app_users 的表，包含列：user_id (NUMBER(8), 主键, 约束名 pk_appuser), username (VARCHAR2(50), 唯一, 非空, 约束名 uq_appuser_uname, nn_appuser_uname), email (VARCHAR2(100), 唯一, 约束名 uq_appuser_email), registration_date (DATE, 默认系统当前日期)。
3. 添加数据文件到表空间：为 user_data_ts 表空间添加一个新的数据文件 /u02/oradata/DBNAME/user_data02.dbf，大小20M。
4. 修改表 - 添加列：为 app_users 表添加一个新列 last_login_ip (VARCHAR2(15))。
5. 修改表 - 修改列类型：将 app_users 表的 email 列长度修改为120个字符。
6. 修改表 - 添加CHECK约束：为 app_users 表添加一个名为 ck_appuser_username_len 的检查约束，确保 username 的长度至少为3个字符。
7. 修改表 - 重命名列：将 app_users 表的 registration_date 列重命名为 join_date。
8. 删除约束：删除 app_users 表上的唯一约束 uq_appuser_email。
9. 删除表并保留回收站：写出删除 app_users 表的SQL语句，确保它进入回收站 (如果回收站功能开启)。
10. 彻底删除表空间：写出彻底删除 user_data_ts 表空间及其所有内容和物理数据文件的SQL语句 (假设里面可能还有其他对象)。

1. 创建表空间解析：

   CREATE TABLESPACE user_data_ts
   DATAFILE '/u02/oradata/DBNAME/user_data01.dbf' SIZE 50M
   AUTOEXTEND ON NEXT 10M MAXSIZE 200M
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   SEGMENT SPACE MANAGEMENT AUTO;
   

   解析：创建了指定大小、自动扩展参数、区及段管理方式的表空间。

2. 创建用户表解析：

   CREATE TABLE app_users (
   user_id NUMBER(8) CONSTRAINT pk_appuser PRIMARY KEY,
   username VARCHAR2(50) CONSTRAINT uq_appuser_uname UNIQUE CONSTRAINT nn_appuser_uname NOT NULL,
   email VARCHAR2(100) CONSTRAINT uq_appuser_email UNIQUE,
   registration_date DATE DEFAULT SYSDATE
   )
   TABLESPACE user_data_ts;
   

   解析：定义了各列的数据类型和约束，包括主键、唯一、非空和默认值，并指定了表空间。

3. 添加数据文件到表空间解析：

   ALTER TABLESPACE user_data_ts
   ADD DATAFILE '/u02/oradata/DBNAME/user_data02.dbf' SIZE 20M;
   

   解析：为现有表空间增加了物理存储容量。

4. 修改表 - 添加列解析：

   ALTER TABLE app_users
   ADD (last_login_ip VARCHAR2(15));
   

   解析：向表中添加了一个新的属性列。

5. 修改表 - 修改列类型解析：

   ALTER TABLE app_users
   MODIFY (email VARCHAR2(120));
   

   解析：修改了email列的最大长度，允许存储更长的邮箱地址。

6. 修改表 - 添加CHECK约束解析：

   ALTER TABLE app_users
   ADD CONSTRAINT ck_appuser_username_len CHECK (LENGTH(username) >= 3);
   

   解析：增加了对username字段长度的业务规则校验。

7. 修改表 - 重命名列解析：

   ALTER TABLE app_users
   RENAME COLUMN registration_date TO join_date;
   

   解析：更改了列的名称，使其更符合业务语义。

8. 删除约束解析：

   ALTER TABLE app_users
   DROP CONSTRAINT uq_appuser_email;
   

   解析：移除了对email列的唯一性要求。

9. 删除表并保留回收站解析：

   DROP TABLE app_users;
   

   解析：默认情况下 (如果回收站开启且未指定PURGE)，DROP TABLE 会将表放入回收站。

10. 彻底删除表空间解析：

    DROP TABLESPACE user_data_ts INCLUDING CONTENTS AND DATAFILES;
    

    解析：此命令会删除表空间、其中所有对象段以及操作系统层面的物理数据文件，操作不可逆，需谨慎。