ORACLE表空间说明及操作

ORACLE 表空间作用

• 数据存储：表空间是数据库中存储数据的逻辑结构。它提供了用于存储表、索引、视图、存储过程等数据库对象的空间。通过划分数据和索引等对象的存储，可以更好地管理和组织数据库的物理存储结构。
• 性能管理和优化：通过将不同类型的数据存储在不同的表空间中，可以实现对数据库的性能管理和优化。例如，可以将热点数据存储在高性能的存储设备上，将历史数据存储在低性能的存储设备上，以提高数据库的读写性能。
• 空间管理：表空间提供了数据库中数据文件的逻辑分离。它允许数据库管理员更好地管理和分配存储空间，以满足数据库的需求。对于大型数据库，可以创建多个表空间并分别指定不同的数据文件，用于灵活管理和扩展数据库的存储容量。
• 简化维护和备份：通过将相关的数据库对象分组到不同的表空间中，可以简化数据库的维护和备份过程。例如，可以对特定表空间执行在线备份或恢复，而不必停止整个数据库服务。
• 安全性和权限管理：表空间还可以用于实现安全性和权限管理。通过分配特定表空间的访问权限，可以限制用户对特定数据的访问和操作。这在多用户环境中非常重要，以确保数据的安全和保密性。

表空间创建

CREATE [TEMPORARY] TABLESPACE hogehoge
DATAFILE ‘<datafile_path>’
SIZE
[AUTOEXTEND ON [NEXT <next_size>] [MAXSIZE <max_size>]]
[LOGGING | NOLOGGING]
[EXTENT MANAGEMENT LOCAL | DICTIONARY]
[SEGMENT SPACE MANAGEMENT AUTO];

以下是各参数说明：TEMPORARY:       带有TEMPORARY就是临时表空间，不带TEMPORARY就是表空间datafile_path: 			【表空间的数据文件路径，用于存储表空间中的数据。】size : 				【设置表空间的初始大小。例如，10M表示10兆字节。】AUTOEXTEND ON:				【表示表空间在用尽时自动扩展。】next_size: 				【如果启用了自动扩展，指定每次扩展的大小。】MAXSIZE:				【表空间允许的最大大小。】LOGGING | NOLOGGING:			【指定表空间是否启用日志记录。】EXTENT MANAGEMENT LOCAL | DICTIONARY:	【指定表空间的Extent管理方式。】SEGMENT SPACE MANAGEMENT AUTO: 		【开启自动段管理。】

创建表空间

语句如下：

例：CREATE TABLESPACE TABLESPACE_NAME DATAFILE SIZE 327.6G;

创建临时表空间

语句如下:

例：CREATE TEMPORARY  TABLESPACE TABLESPACE_NAME DATAFILE SIZE 327.6G;

查看相关表空间属性。

利用以下SQL查询。

select TABLESPACE_NAME,LOGGING,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT,BIGFILE 
from dba_tablespaces where tablespace_name ='XXXX';

表空间删除：

移动表空间

首先，表空间要删除表空间中对象（表、索引等）移动到其他表空间中，然后删除

ALTER TABLE table_name MOVE TABLESPACE new_tablespace;					
ALTER INDEX index_name REBUILD TABLESPACE new_tablespace;	

删除表空间：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;					
DROP TABLESPACE tablespace_name INCLUDING CONTENTS;		

DROP TABLESPACE语句选项解说						●INCLUDING CONTENTS			指定在删除表空间之前同时删除其中包含的所有对象，如表、索引、约束等。这是 DROP TABLESPACE 的默认行为，确保表空间及其内容都被完全删除。●INCLUDING CONTENTS AND DATAFILES	这是 INCLUDING CONTENTS 的进一步扩展，除了删除表空间中的对象，还会删除与表空间关联的数据文件。删除数据文件后，数据库会释放相应的磁盘空间。●KEEP DATAFILES				用于指定在删除表空间时保留表空间中的数据文件，不删除与表空间相关联的数据文件。这在迁移或重新组织数据库时可能会有用，因为数据文件可以在其他表空间中重新使用。●INCLUDING ROLLBACK SEGMENTS		同时删除表空间中的回滚段。回滚段是用于支持事务回滚和数据一致性的重要组件。DROP TABLESPACE 默认会忽略回滚段并仅删除表空间本身。 ●FORCE					强制删除表空间，即使表空间处于活动状态或某些对象无法正常删除。此选项可能会导致数据丢失或破坏，请谨慎使用。●CASCADE CONSTRAINTS			在删除表空间及其内容时，连带删除所有依赖于这些对象的约束。这包括外键约束、引用约束等。选择此选项将确保数据的完整性。

表空间大小变更：

alter tablespace hogehoge2 resize 200M;				

查看当前原定临时表空间

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

创建新的临时表空间：

CREATE TEMPORARY TABLESPACE_NAME example-tablespace TEMPFILE SIZE 100M;

将新的临时表空间设置为原定设置临时表空间：

EXEC RDSADMIN.RDSADMIN_UTIL.ALTER_DEFAULT_TEMP_TABLESPACE(TABLESPACE_NAME => 'example-tablespace');

创建用户

创建用户

create user USER_NAME identified by 1234567;

修改用户：

alter user USER_NAME identified by NEW_PASSWORD;

删除用户：

drop user NEW_USER_NAME ;

修改密码:

alter user USER_NAME identified by NEW_PASSWORD;

查看一下所有用户所在的表空间；

select username,default_tablespace from dba_users;

密码过期设定

oracle每隔180天后就登录不了，进行密码不过期的设置。

①、查看用户的proifle是哪个，一般是default：SELECT username,PROFILE FROM dba_users;②、查看指定概要文件（如default）的密码有效期设置：SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';③、将密码有效期由默认的180天修改成“无限制”：ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;修改之后不需要重启动 数据库，会立即生效。④、用户登录时，如果报“ORA-28000:用户已被锁”，则需要解锁alter user xxx account unlock;commit;

将表空间分配给用户

保存数据的默认表区域:default_tablespace
alter user USER_NAME default tablespace default_tablespace
默认设置的临时表区域:temporary_tablespace
ALTER USER USER_NAME TEMPORARY TABLESPACE temporary_tablespace ;

注意： Oracle 默认表空间存储数据库中的各种对象，如表、索引、视图、存储过程等。
如果在创建索引的时候需要存储到其他表空间，在创建过程中需要指定表空间，这样就不会存储到默认表空间中,语句如下：

CREATE INDEX index_name ON table_name(column_name) TABLESPACE tablespace_name;

给用户授权：

--赋予create session的权限,注意这句一定要有grant create session to USER_NAME;		--分配创建表，视图，触发器，序列，过程 权限						grant create table,create view,create trigger, create sequence,create procedure to USER_NAME;	--授权使用表空间grant unlimited tablespace to USER_NAME ; 							或者grant connect,resource,dba to USER_NAME ;

创建REDO日志文件

概述

REDO日志件是Oracle数据库中的一种重要文件类型，用于记录数据库的事务操作。它是用于实现事务持久性和恢复性的关键组成部分。

在Oracle数据库中，当进行数据修改操作（例如插入、更新或删除数据）时，相应的REDO记录会被写入REDO日志缓冲区，然后以顺序的方式写入REDO记录文件中。这种顺序写入的方式可以提高性能，同时也确保了事务的持久性。

REDO记录文件记录了数据库发生的所有事务操作，包括对表的变更、索引的更新等。它们被用于在数据库恢复期间重做丢失的事务操作，以确保数据库的一致性和完整性。

在Oracle数据库中，REDO日志文件有固定的大小，并以循环方式使用。当最后一个REDO记录文件被写满后，下一个记录将从第一个文件开始继续写入，形成一个循环。

查询REDO日志文件的设定

SQL：	SELECT * FROM v$log JOIN v$logfile USING (group#) ORDER BY group#;

添加REDO日志文件

SQL： exec rdsadmin.rdsadmin_util.add_logfile('1024M');

删除REDO日志文件

●1.删除REDO日志文件，文件的状态必须是INACTIVE。
如果要删除的文件的状态是“CURRENT”，请用下面的命令将其他文件设置为“CURRENT”。

SQL : exec rdsadmin.rdsadmin_util.switch_logfile;

●2.待删除文件的STATUS变成了“ACTIVE”。待删除文件的状态 “ACTIVE”→“INACTIVE”，
如果待删除文件状态是INACTIVE，请跳过下面的执行
请执行以下命令。

SQL : exec rdsadmin.rdsadmin_util.checkpoint;

●3.用下面的命令删除日志文件。

SQL : exec rdsadmin.rdsadmin_util.drop_logfile(XX);

完成

---

其他sql查询

1、查看当前用户拥有的角色权限信息：select * from role_sys_privs;2、查看当前用户的详细信息：select * from user_users;3、修改用户密码：alter user 用户名 identified by 新密码；4、查询所有表空间物理位置
select name from v$datafile;5、查询当前用户的表空间
select username,default_tablespace from user_users;6、修改用户的默认表空间
alter user 用户名 default tablespace 新表空间; 7、查询所有的表空间
select * from user_tablespaces; 8、查询用户权限信息
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = USER_NAME; 9、撤销用户权限
REVOKE create session  FROM USER_NAME;
REVOKE CREATE TABLE  FROM USER_NAME;
REVOKE CREATE VIEW  FROM USER_NAME;
REVOKE CREATE PROCEDURE  FROM USER_NAME;
REVOKE UNLIMITED TABLESPACE  FROM USER_NAME;
REVOKE CREATE SEQUENCE  FROM USER_NAME;
REVOKE CREATE TRIGGER  FROM USER_NAME;10、删除用户
DROP USER USER_NAME CASCADE;

概念补充说明：

1、EXTENT MANAGEMENT:

	在 Oracle 表领域，EXTENT MANAGEMENT 是用于管理表空间（tablespace）中的数据块（data block）分配的特性之一。EXTENT 是一组连续的数据块，用于存储表中的行数据。EXTENT MANAGEMENT 指定了数据块在表空间中如何分配和管理。Oracle 数据库提供了两种类型的 EXTENT MANAGEMENT：DICTIONARY（字典管理）和 LOCAL（本地管理）。DICTIONARY EXTENT MANAGEMENT：使用数据库字典记录来管理数据块分配。在 DICTIONARY EXTENT MANAGEMENT 模式下，所有的表空间共享一个全局的字典。LOCAL EXTENT MANAGEMENT：使用每个表空间自己的位图（bitmap）来管理数据块分配。在 LOCAL EXTENT MANAGEMENT 模式下，每个表空间都维护自己的独立位图。要指定表空间的 EXTENT MANAGEMENT 类型，在创建表空间时，可以使用以下语法进行设置：CREATE TABLESPACE tablespace_name[EXTENT MANAGEMENT {LOCAL | DICTIONARY}];上述语法中，“tablespace_name” 是要创建的表空间的名称。通过指定 “EXTENT MANAGEMENT” 参数的值为 “LOCAL” 或 “DICTIONARY”，可以选择相应的 EXTENT MANAGEMENT 类型。例如，创建一个名为 “my_tablespace” 的表空间，并指定使用 LOCAL EXTENT MANAGEMENT：CREATE TABLESPACE my_tablespaceEXTENT MANAGEMENT LOCAL;

2、SEGMENT SPACE MANAGEMENT：

	在 Oracle 数据库中，SEGMENT SPACE MANAGEMENT 是用于管理数据段（segment）分配和空间回收的特性之一。在 Oracle 9i 之前的版本中，使用 FREELIST（逐行管理）进行段空间管理。这种方法要求维护每个数据块内部的空闲列表，以跟踪可用空间和空闲块。然而，这种管理方式在大型表或高并发情况下效率较低。从 Oracle 9i 开始，引入了 SEGMENT SPACE MANAGEMENT，提供了两种管理模式：MANUAL（手动管理）和 AUTO（自动管理）。MANUAL SEGMENT SPACE MANAGEMENT：在 MANUAL 模式下，数据库管理员（DBA）需要手动管理段的空间分配和释放。这意味着需要手动执行分配和释放空间的操作。AUTO SEGMENT SPACE MANAGEMENT：在 AUTO 模式下，数据库会自动管理段的空间分配和释放。数据库会根据需要自动分配和释放块，并维护一个位图（bitmap）来跟踪空闲空间。这种自动管理方式提高了性能和可伸缩性。要指定段空间管理模式，可以在创建表或索引时使用以下语法：CREATE TABLE table_name (column1 datatype,column2 datatype,...) SEGMENT SPACE MANAGEMENT {MANUAL | AUTO};在上述语法中，“table_name” 是要创建的表的名称，“column1”、“column2” 等是表的列名和对应的数据类型。通过使用 “SEGMENT SPACE MANAGEMENT” 参数，可以选择 MANUAL 或 AUTO 模式。例如，在创建一个名为 “employees” 的表时，使用 AUTO SEGMENT SPACE MANAGEMENT：CREATE TABLE employees (employee_id NUMBER,first_name VARCHAR2(50),last_name VARCHAR2(50)) SEGMENT SPACE MANAGEMENT AUTO;

3、系统表空间有哪些：

	Oracle数据库中，表空间（Tablespace）用于管理数据文件，它们存储了表、索引以及其他数据库对象的数据。Oracle数据库支持多种类型的表空间，每种表空间类型有其特定的用途和属性。以下是一些常见的Oracle表空间类型及其具体区别：●SYSTEM 表空间：这是数据库的核心表空间，包含了数据库的系统数据和元数据，如数据字典、系统表等。通常情况下，这个表空间应该只包含系统级别的数据，而不应该用于存储用户数据。在一个数据库中，只能有一个SYSTEM表空间。●SYSAUX 表空间：SYSAUX表空间用于存储一些附加的系统级数据，例如Oracle Enterprise Manager（OEM）的性能统计数据、日志等。这个表空间的引入有助于减轻SYSTEM表空间的负担，提高性能和可维护性。●TEMP 表空间：TEMP表空间用于存储临时数据，例如在查询执行期间创建的临时表或排序中使用的临时空间。多个用户可以共享一个TEMP表空间。●UNDO 表空间：UNDO表空间用于存储回滚段（Undo Segment），以支持事务的回滚和并发控制。每个数据库实例可以有一个或多个UNDO表空间，这取决于数据库配置。●User表空间：用户表空间用于存储用户数据，包括表、索引和其他用户创建的数据库对象。用户可以创建自己的表空间，用于组织和管理他们的数据。●RDSADMIN 表空间:Amazon RDS（Relational Database Service）的特殊表空间，它是用于存储Amazon RDS数据库管理所需的对象和数据的表空间。 Amazon RDS是Amazon Web Services（AWS）提供的托管关系型数据库服务，它简化了数据库管理任务，包括备份、恢复、升级和性能调整等。●临时用户表空间：临时用户表空间类似于TEMP表空间，但是每个用户可以有自己的临时表空间，用于存储用户级别的临时数据。●索引表空间：这是一种特殊的用户表空间，用于存储索引数据，以分离索引数据和用户数据。●LOB 表空间：用于存储大对象（LOB）数据类型，如CLOB和BLOB。

---

大鹏一日同风起，扶摇直上九万里。
加油诸位