Oracle数据库常用命令

数据库初始化命令

表空间相关

建立表空间

--建立临时表空间
CREATE TEMPORARY TABLESPACE temp_name --创建名为temp_name的临时表空间
TEMPFILE 'D:\app\super\oradata\oracle\temp_name.DBF'    -- 临时文件
SIZE 50M    -- 其初始大小为50M
AUTOEXTEND ON    -- 支持自动扩展
NEXT 50M MAXSIZE 20480M    -- 每次增量为50M ,最大2048M
EXTENT MANAGEMENT LOCAL;
  
--建立表空间
CREATE TABLESPACE test_name --创建名为"test_name"的表空间
LOGGING
DATAFILE 'D:\app\super\oradata\oracle\test_name.DBF'
SIZE 50M    -- 其初始大小为50M
AUTOEXTEND ON    -- 支持自动扩展
NEXT 50M MAXSIZE 20480M    -- 每次增量为50M ,最大2048M
EXTENT MANAGEMENT LOCAL;

查询表空间

-- 查询所有表空间
select * from Dba_Tablespaces;

-- 查询表空间名称及大小
SELECT
	t.tablespace_name,
	round( SUM( bytes / ( 1024 * 1024 ) ), 0 ) ts_size 
FROM
	dba_tablespaces t,
	dba_data_files d 
WHERE
	t.tablespace_name = d.tablespace_name 
GROUP BY
	t.tablespace_name;

-- 查看表空间物理文件的名称及大小
SELECT
	tablespace_name,
	file_id,
	file_name,
	round( bytes / ( 1024 * 1024 ), 0 ) total_space 
FROM
	dba_data_files 
ORDER BY
	tablespace_name;

-- 查看表空间的使用情况
SELECT a.tablespace_name "表空间名",
       round(total / (1024 * 1024 * 1024), 2) "表空间大小(G)",
       round(free / (1024 * 1024 * 1024), 2) "表空间剩余大小(G)",
       round((total - free) / (1024 * 1024 * 1024), 2) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

删除表空间

--删除空的表空间，但是不包含物理文件
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;

扩容表空间

-- 允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;

-- 增加数据文件
ALTER TABLESPACE SYSTEM ADD DATAFILE
'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\O2_MF_SYSTEM_CWMNZ9XV_.DBF' 
size 7167M autoextend on ;

用户相关

-- 创建用户并关联表空间
CREATE USER userName IDENTIFIED BY password -- 12c之后的版本，可以试试在用户名前加上c##
DEFAULT TABLESPACE test_name  -- 默认表空间
TEMPORARY TABLESPACE temp_name; -- 临时表空间，关联任意一个都行
 
-- 创建表的时候没有关联表空间
CREATE USER userName IDENTIFIED BY PASSWORD;
CREATE USER C##userName IDENTIFIED BY PASSWORD; -- 应该是12c之后都需要
 
-- 修改用户表空间
ALTER USER username DEFAULT TABLESPACE test_name;

-- 赋权，普通用户
grant connect, resource to user1;

-- 赋权，管理员
grant dba to user1;

-- 取消权限
revoke connect, resource from user;