管理表（五）

远歌已逝

于 2024-12-04 19:45:07 发布

阅读量660

点赞数 17

分类专栏：数据库文章标签：数据库 oracle 服务器

本文链接：https://blog.youkuaiyun.com/yuanymnl/article/details/144233207

版权

数据库专栏收录该内容

32 篇文章

订阅专栏

创建临时表的例子

SQL> show user
USER is "AS1"
 
SQL> desc t;
Name        Null?    Type
----------- -------  ------
ID          NUMBER(38)
NAME        CHAR(10)

SQL> create global temporary table tmp_session as
ERROR at line 1:
ORA-00928: missing SELECT keyword

SQL> create global temporary table tmp_session on commit preserve rows
  2  as select * from t where 1=0;
Table created.

SQL> create global temporary table tmp_transaction on commit delete rows
  2  as select * from t where 1=0;
Table created.

SQL>

tmp_session:

使用 ON COMMIT PRESERVE ROWS。
数据在整个会话期间可见，直到会话结束。
适用于需要在多个事务中共享数据的场景。

tmp_transaction:

使用 ON COMMIT DELETE ROWS。
数据在每次提交后被删除，仅在当前事务内可见。
适用于只需要在单个事务中使用数据的场景。

上边的代码的功能

查看当前用户：
通过 show user 命令查看当前登录数据库的用户是谁，这里显示当前用户为 "AS1"。
查看表结构：
使用 desc t 命令查看名为 t 的表的结构，从输出可以知道表 t 包含两列，列名分别为 ID（数据类型是 NUMBER(38)）和 NAME（数据类型是 CHAR(10)）。
尝试创建临时表（第一次尝试失败）：
执行 create global temporary table tmp_session as 语句想要创建一个全局临时表 tmp_session，但这条语句报错了，错误提示为 ORA-00928: missing SELECT keyword，原因是创建临时表并基于已有表结构和数据填充时（这里采用的是类似的方式），需要在 as 后面跟上 select 语句来指定数据来源，而此处遗漏了，所以创建失败。
创建会话级临时表：
执行 create global temporary table tmp_session on commit preserve rows as select * from t where 1=0; 创建了一个名为 tmp_session 的全局临时表，并且指定了 on commit preserve rows 子句，这意味着该临时表是会话级临时表，也就是在当前会话期间，表中的数据会一直保留，即使执行了 commit 操作（提交事务），数据也依然存在，直到会话结束才会被清除。这里 select * from t where 1=0 表示先按照 t 表的结构来创建临时表，但并不会从 t 表中实际导入数据（因为 1=0 这个条件永远不成立）。
创建事务级临时表：
执行 create global temporary table tmp_transaction on commit delete rows as select * from t where 1=0; 成功创建了一个名为 tmp_transaction 的全局临时表，通过 on commit delete rows 子句指定其为事务级临时表，即当执行 commit 操作（提交事务）后，临时表中的数据就会被自动删除，同样也是按照 t 表结构创建但暂不导入实际数据（基于 where 1=0 这个条件）。

修改存储和块利用率参数

在Oracle数据库中，可以通过ALTER TABLE命令修改表的存储和块利用率参数。这些参数影响表的存储分配和数据块的使用效率。

1. 语法

ALTER TABLE [schema.]table
{[ storage-clause ]
[ INITRANS integer ]
[ MAXTRANS integer]}

2. 存储参数的影响

NEXT:
- 定义分配给表的下一个extent的大小。
- 当Oracle服务器为表分配另一个extent时，将使用新的NEXT值。
- 后续的extent大小将根据PCTINCREASE进行增加。
PCTINCREASE:
- 记录在数据字典中，用于重新计算下一个extent的大小。
- 例如，若表有两个extent，NEXT=10K，PCTINCREASE=0，若将PCTINCREASE更改为100，则：
  - 第三个extent为10K
  - 第四个extent为20K
  - 第五个extent为40K
  - 依此类推。
MINEXTENTS:
- 可以更改为小于或等于当前extent数量的任何值。
- 对表没有立即影响，但在表被截断时会使用该值。
MAXEXTENTS:
- 可以设置为大于或等于当前extent数量的任何值，也可以设置为UNLIMITED。
- 控制表可以扩展的最大extent数量。

3. 限制条件

INITIAL:
- 表的INITIAL值不能被修改。
NEXT:
- 指定的NEXT值将被四舍五入为大于或等于指定值的块大小的倍数。

块利用率参数

PCTFREE:
- 定义每个数据块中保留的空闲空间百分比，以便将来更新。
- 较高的PCTFREE值可以减少块的密度，适用于经常更新的列。
PCTUSED:
- 定义块在被返回到空闲列表之前必须使用的最小百分比。
- 确保块在有足够空间容纳新行时才会被重新利用。

选择和调整建议

PCTFREE和PCTUSED的设置应根据表的使用模式进行调整：
- 如果表中的数据经常更新，考虑增加PCTFREE。
- 如果表的插入操作频繁，适当降低PCTFREE和提高PCTUSED可以提高空间利用率。
NEXT和PCTINCREASE的设置应考虑到表的增长模式：
- 如果预计表会快速增长，可以设置较大的NEXT和PCTINCREASE值。
- 反之，如果表的增长较慢，可以设置较小的值以节省空间。

手动分配Extent

操作:
- 使用ALTER TABLE命令手动为表分配extent。
- 可以控制表的extent在不同文件之间的分布。
- 在大量加载数据之前手动分配extent，避免表的动态扩展。

语法:

ALTER TABLE [schema.]table
ALLOCATE EXTENT [([SIZE integer [K|M]] [DATAFILE 'filename'])]

DATAFILE子句中指定的文件必须属于该表所属的表空间。否则，该语句将生成一个错误。如果没有使用DATAFILE子句，Oracle服务器将在包含表的表空间中的一个文件中分配区段。
注意：DBA_TABLES中的NEXT_EXTENT值不会受到手动区段分配的影响。执行此命令时，Oracle服务器不会重新计算下一个区段的大小。

注意事项:
- 如果未指定SIZE，Oracle服务器将使用DBA_TABLES中的NEXT_EXTENT大小来分配extent。
- DATAFILE子句中指定的文件必须属于表所在的表空间，否则会生成错误。

非分区表重组

操作:
- 对非分区表进行重组时，保留表的结构但不保留内容。
- 可以将表移动到不同的表空间或重新组织extent。

语法:

ALTER TABLE hr.employees
MOVE TABLESPACE data1;

用途:
- 可以在不运行导出或导入工具的情况下移动非分区表。
- 允许更改存储参数，如移动表到不同的表空间或消除行迁移。

非分区表重组
无需运行Export或Import实用程序就可以移动非分区表。在此外，它还允许更改存储参数。这在以下情况下很有用：

将表从一个表空间移动到另一个表空间
重新组织表以消除行迁移
移动表后，必须重建索引以避免以下错误：

SQL> select * from employees where id = 23;
select * from employees where id = 23
              *
ERROR at line 1:
ORA-00942: table or view does not exist

截断表

操作:
- 截断表会删除表中的所有行并释放已使用的空间。
- 相应的索引也会被截断。
语法:
```
TRUNCATE TABLE hr.employees;
```
效果:

删除表中的所有行。
由于TRUNCATE TABLE是一个DDL命令，因此不会生成undo数据，并且隐式地提交命令。
相应的索引也被截断。
被外键引用的表不能被截断。
使用此命令时，删除触发器不会触发。

删除表

操作:
- 使用DROP TABLE命令删除表，释放表使用的extent。
- 可以自动或手动合并连续的extent。

语法:

DROP TABLE hr.departments CASCADE CONSTRAINTS;

注意事项:
- 删除表时，表所使用的区段将被释放。如果它们是连续的，则可以在稍后的阶段自动或手动合并它们如果表是外键关系中的父表，则CASCADE CONSTRAINTS选项是必需的。
- 有关级联约束的详细信息，请参阅“维护数据完整性”一课。
  以上内容涵盖了手动分配extent、非分区表重组、截断表和删除表的操作及相关注意事项。

删除列

ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;  # 避免undo空间被撑爆，每删除1000行产生一个CHECKPOINT

从表中删除列：
- 从每行中删除列长度和数据，释放数据块中的空间。
- 在大型表中删除列需要相当长的时间。

在数据库中删除列会导致数据行中的列长度和数据被删除，从而释放数据块中的空间。值得注意的是，在大型表中删除列可能需要较长的时间。

Oracle服务器允许从表的行中删除列。删除列可以清除未使用的和可能需要空间的列，而无需导出或导入数据，并重新创建索引和约束。
删除列可能会花费大量时间，因为该列的所有数据都将从表中删除。
在Oracle8i之前，不可能从表中删除列。
在删除列时使用检查点
删除列可能非常耗时，并且需要大量的撤消空间。在从大型表中删除列时，可以指定检查点以最小化撤销空间的使用。在幻灯片中的示例中，检查点每1000行出现一次。在操作完成之前，表被标记为无效。参考DBA-OBJECTS视图中的STATUS列。如果实例在操作过程中失败，则表在启动时保持INVALID状态，并且必须完成操作。
使用以下语句恢复中断的删除操作：
   SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;
如果表处于VALID状态，使用此方法将产生一个错误。

1. 重命名列 (Renaming a Column)

在 Oracle 数据库中，列重命名是通过 ALTER TABLE 语句进行的。以下是重命名列的基本语法：

ALTER TABLE [schema.]table_name
RENAME COLUMN old_column_name
TO new_column_name;

注意事项：

仅能在关系型表中重命名列，不能在具有连接索引（join indexes）的表中重命名列，必须先删除相关的索引。
该操作会导致与列相关的视图、触发器、函数、存储过程和包失效。您需要手动修复它们，确保使用新列名。
对于物化视图和涉及复制的表，重命名操作是允许的，但可能需要更新物化视图以避免后续错误。

2. 使用 UNUSED 选项

标记列为 unused:
通过将列标记为未使用（unused），您可以延迟实际删除列的操作。标记为 unused 后，列的数据不会被删除，但也不会对查询结果产生影响，且列不会出现在 DESCRIBE 命令的输出中。此方法的优势是操作较为迅速，因为它不会立即回收磁盘空间。

标记列为 unused 的语法如下：

ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

删除 unused 列:

ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;

CHECKPOINT 1000：表示在删除列时以 1000 行为单位执行操作。
DROP COLUMNS CONTINUE CHECKPOINT 1000：继续执行列删除操作，直到所有列被删除。

优势：

如果有多个列需要删除，首先将它们标记为 unused，然后再删除，这样只会更新一次表中的数据。

标记为 unused 的列的行为：

标记为 unused 的列不参与查询，列的名称和数据类型不再出现在 DESCRIBE 输出中。
可以在表中添加与 unused 列同名的新列。

查询含有 unused 列的表：

SELECT * FROM dba_unused_col_tabs;

此查询可以返回所有具有 unused 列的表及其 unused 列的数量。

3. 删除列的限制 (Restrictions on Dropping a Column)

Oracle 数据库在删除列时有若干限制，具体包括：

不能删除对象类型表中的列。
不能删除嵌套表（nested tables）中的列。
不能删除表中所有列。
不能删除分区键列（partitioning key column）。
不能删除 SYS 用户拥有的表中的列。
不能删除索引组织表（IOT，Index-Organized Table）中的主键列。
如果表中有 LONG 或 LONG RAW 列标记为 unused，但未被删除，则不允许再添加 LONG 或 LONG RAW 列。

4. 获取表信息

Oracle 提供了一些数据字典视图，帮助管理员获取关于表的元数据：

DBA_TABLES：查询所有表的信息。
DBA_OBJECTS：查询所有对象的信息。

例如，查询 HR 用户下的所有表：

SELECT table_name FROM dba_tables WHERE owner = 'HR';

查询表的对象信息：

SELECT object_name, created FROM DBA_OBJECTS WHERE object_name LIKE 'EMPLOYEES' AND owner = 'HR';

获取对象的创建语句

在 Oracle 数据库中，查询一个对象（如表、视图、索引、存储过程等）的创建语句是一个常见的需求。Oracle 提供了多种方式来获取对象的创建信息，下面是几种常用的查询方法：

1. 查询数据库对象的创建语句（DBA_OBJECTS 视图）

DBA_OBJECTS 视图提供了对象的元数据，但它不直接提供创建语句。不过，您可以根据对象类型和其他信息来推测创建语句。要查询某个对象的相关信息，可以执行以下 SQL：

SELECT object_name, object_type, created
FROM dba_objects
WHERE owner = 'SCHEMA_NAME' AND object_name = 'OBJECT_NAME';

例如，查询 HR 用户下名为 EMPLOYEES 的对象信息：

SELECT object_name, object_type, created
FROM dba_objects
WHERE owner = 'HR' AND object_name = 'EMPLOYEES';

2. 使用 `DBMS_METADATA.GET_DDL` 获取创建语句

要获得具体的创建语句（例如表、视图、索引等），Oracle 提供了 DBMS_METADATA.GET_DDL 函数，这个函数可以用来生成任何数据库对象的 CREATE 语句。

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEES', 'HR') 
FROM dual;   # 查询表的创建语句
- `TABLE` 是对象类型。
- `EMPLOYEES` 是对象名称。
- `HR` 是拥有者（schema）。

SELECT dbms_metadata.get_ddl('VIEW', 'VIEW_NAME', 'SCHEMA_NAME') 
FROM dual;   # 查询视图的创建语句

SELECT dbms_metadata.get_ddl('INDEX', 'INDEX_NAME', 'SCHEMA_NAME') 
FROM dual;    # 查询索引的创建语句

SELECT dbms_metadata.get_ddl('PROCEDURE', 'PROCEDURE_NAME', 'SCHEMA_NAME') 
FROM dual;     # 查询存储过程/函数的创建语句

SELECT dbms_metadata.get_ddl('TRIGGER', 'TRIGGER_NAME', 'SCHEMA_NAME') 
FROM dual;     # 查询触发器的创建语句

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEES', 'HR') 
FROM dual;    # 查询 `EMPLOYEES` 表的创建语句

3. 查看数据库对象的结构（DESCRIBE 命令）

如果只是需要查看表或其他对象的结构，而不是完整的创建语句，可以使用 DESCRIBE 命令。这个命令不会显示创建语句，但它会列出表的所有列、数据类型、约束等信息。

DESCRIBE hr.employees;

4. 使用 Oracle SQL Developer 或 TOAD 工具

如果你使用图形化工具（如 Oracle SQL Developer 或 TOAD），它们通常会提供查看对象创建脚本的功能。在这些工具中，右键点击某个对象（如表、视图等），然后选择查看或生成创建脚本（通常是 “Generate DDL” 或类似的选项）。

5. 查询 `USER_OBJECTS` 视图（仅限当前用户）

如果你只需要查询当前用户下的对象，可以使用 USER_OBJECTS 视图，它类似于 DBA_OBJECTS，但它只包含当前用户的对象信息。

SELECT object_name, object_type, created
FROM user_objects
WHERE object_name = 'EMPLOYEES';