ORACLE表空间、用户、表结构相关sql整理

前言

工作以来用到过的sql，整理到这里，方便自己和路人随时查阅。

一、表空间

1. 创建临时表空间

• data_temp 临时表空间名字
• /oracle/data/ 存放数据库文件的地方，一般是安装数据库后有控制文件，数据文件和日志文件的文件夹，再加上要创建表空间的名字+dbf （数据文件）
• 10240m 表空间的初始大小
• 100m 表空间的自动增长大小
• 102400m 表空间最大的大小 unlimited 无限制扩容

   create temporary tablespace data_temp tempfile'/oracle/data/data_temp.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local;

2. 创建数据表空间

   create tablespace data_dir logging datafile'/oracle/data/data_file.dbf' size 1024m autoextend on next 100m maxsize 10240m extent management local;

3. 设置表空间为无限增长

   ALTER DATABASE DATAFILE '/oracle/data/data_file.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED;

4. 删除表空间

   --以system用户登录：
   --查找工作空间的路径
   select file_name,tablespace_name from dba_data_files;
   --查找临时表空间
   select file_name,tablespace_name from dba_temp_files;
   
   --删除空的表空间，但是不包含物理文件
   drop tablespace tablespace_name;
   --删除非空表空间，但是不包含物理文件
   drop tablespace tablespace_name including contents;
   --删除空表空间，包含物理文件
   drop tablespace tablespace_name including datafiles;
   --删除非空表空间，包含物理文件
   drop tablespace tablespace_name including contents and datafiles;
   --如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
   drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;
   --删除表空间，及对应的表空间文件也删除掉
   drop tablespace ABC including contents and datafiles cascade constraint;

5. 查询用户属于哪个表空间

   select username,default_tablespace from dba_users where username= '用户名';

6. 查询可用空间情况

   SELECT TOTAL.TABLESPACE_NAME AS 表空间名,
          ROUND(TOTAL.MB - FREE.MB, 2) || ' MB' AS 当前已用空间,
          ROUND(FREE.MB, 2) || ' MB' AS 当前可用空间,
          ROUND(TOTAL.MB, 2) || ' MB' AS 当前可用的总空间,
          ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS 当前使用百分比,
          ROUND(TOTAL.MAX_MB, 2) || ' MB' AS 可扩展到的最大空间,
          DECODE(TOTAL.MAX_MB, 0, 0, ROUND(TOTAL.MAX_MB - TOTAL.MB, 2)) ||
          ' MB' AS 剩余可扩展的空间,
          DECODE(TOTAL.MAX_MB,
                 0,
                 0,
                 ROUND((1 - TOTAL.MB / TOTAL.MAX_MB) * 100, 2)) || '%' AS 剩余可扩展的百分比,
          ROUND(TOTAL.MAX_MB - FREE.MB, 2) || ' MB' AS 剩余可用的最大空间
     FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
             FROM DBA_FREE_SPACE
            GROUP BY TABLESPACE_NAME) FREE,
          (SELECT TABLESPACE_NAME,
                  SUM(BYTES) / 1024 / 1024 AS MB,
                  SUM(MAXBYTES) / 1024 / 1024 AS MAX_MB
             FROM DBA_DATA_FILES
            GROUP BY TABLESPACE_NAME) TOTAL
    WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
    ORDER BY TOTAL.TABLESPACE_NAME

二、用户

1. linux登录oracle

su - oracle
sqlplus / as sysdba

2. 创建用户

• test 用户名
• abc123456 用户密码
• data_dir 表空间名
• data_temp 临时表空间名

create user test identified by abc123456 default tablespace data_dir temporary tablespace data_temp;

3. 修改用户密码

--查看用户列表
select username from dba_users;
--修改某个用户密码
alter user 用户名 identified by 新密码;
--如 alter user TEST identified by abc123456;

4. 修改用户名

--1.sysdba登录
sqlplus / as sysdba
--2.查询所有用户
SELECT user#,name FROM user$;
--3.修改需要更改的用户名
UPDATE USER$ SET NAME='新的用户名' WHERE user#=71;
COMMIT;
--4.强制刷新
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH SHARED_POOL;
--5.将新的用户名对应的密码修改下
ALTER USER 新用户名 IDENTIFIED BY 密码;

5. 解锁某个用户

--查看用户列表
select username from dba_users;
alter user 用户名 account unlock;

6. 删除用户

drop user 用户名 cascade;
--如果无法删除可能因为用户还有会话连接，需要删除会话(此处用户名区分大小写)
select sid,serial# from v$session where username='用户名';
alter system kill session '150,9019';

7. 查询密码过期时间

select username,account_status,expiry_date,profile from dba_users where username = 'TEST';

三、表结构

1. 添加字段

- 数据类型样例：varchar2(20)

alter table 表名 add 字段名 数据类型;

2. 添加注释

comment on column 表名.字段名 is '注释内容';

3. 修改字段名

alter table 表名 rename column 旧字段名 to 新字段名;

4. 修改字段长度

alter table 表名 modify 字段名 数据类型;

5. 设置默认值

alter table 表名 modify 字段名 default 默认值;

6. 添加表注释

comment on table 表名 is '表注释';

四、其他

主键自增

--demo_seq为序列名称
create sequence demo_seq increment by 1  start with 1 nomaxvalue nominvalue nocache;
--demo为表名,id 为主键
create or replace trigger demo_seq  
before insert on demo for each row 
begin 
	select demo_seq.nextval into :new.id from dual; 
end;