使用declare匿名块创建表空间和用户

1、匿名块和命名块

1.1、匿名块

使用declare或begin关键字开头的即匿名块，每次使用均需要进行编译，不能存储在数据库中且不能被其他PL/SQL调用。

1.2、命名块

存储过程，存储函数，触发器等即命名块，一经编译后面就可以直接调用，且可以存储在数据库中，被其他PL/SQL调用。

2、创建表空间

2.1、创建表空间自动扩容

-- 创建表空间DATA_DEMO
declare
--声明一个参数v_rowcount类型为integer
  v_rowcount integer;
begin
--查询存在DATA_DEMO的表空间，求和并放到v_rowcount中
  select count(*) into v_rowcount from dual where exists(select * from v$tablespace a where a.name = upper('DATA_DEMO'));
  if v_rowcount > 0 then 
  	-- 当v_rowcount大于0,删除DATA_DEMO表空间包括磁盘空间
    execute immediate 'DROP TABLESPACE DATA_DEMO INCLUDING CONTENTS AND DATAFILES';
  end if;
end;
/
-- 创建初始值为20M的DATA_DEMO表空间，磁盘空间名为DATA_DEMO.dbf
-- ${Oracle}是Oracle安装目录
CREATE TABLESPACE DATA_DEMO DATAFILE '${Oracle}\oradata\orcl\DATA_DEMO.dbf' size 20M
-- 自增且每次增加20M，最大没有上限（最大32G）
AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
-- 数据区本地管理(默认为本地管理)
EXTENT MANAGEMENT LOCAL
-- 数据段自动管理
SEGMENT SPACE MANAGEMENT AUTO;

2.2、手动扩容表空间

dba_data_files表的字段及说明

字段	说明
FILE_NAME	文件名字(物理地址)
FILE_ID	文件ID，整个数据库中每个文件ID都是唯一的
TABLESPACE_NAME	文件所属的表空间，Oracle中每个数据文件都和表空间是对应的
BYTES	文件字节数(换算成MB:bytes/(1024*2))
BLOCKS	文件的块数量，和BYTES是可以换算的(BYTES/1024/BLOCK_SIZE得到BLOCKS的数量)
STATUS	状态:表示文件是否可用
RELATIVE_FNO	相对文件号;相对文件号只在表空间唯一，每个表空间都有自己的相对文件号
AUTOEXENTSIBLE	是否可以扩展
MAXBYTES	如果可以扩展，最大可以到多大(11g、12C是32G)
MAXBLOCKS	如果可以扩展，最大可以多少数据块
INCREMENT_BY	每次增加的块数量
USER_BYTES	文件中实际有用的字节数
USER_BLOCKS	文件中实际有用的块
ONLINE_STATUS	在线状态

-- 查询表空间存放位置及大小还有是否自动扩容
select tablespace_name 表空间名,file_id 文件id,file_name 文件名字,round(bytes/(1024*1024),0) "文件大小(MB)",autoextensible 是否可以扩展 from dba_data_files where tablespace_name = upper('DATA_DEMO');

-- 表空间扩容300M
ALTER TABLESPACE DATA_DEMO ADD DATAFILE 'DATA_DEMO.DBF' SIZE 20M;

3、创建及删除用户

3.1、创建用户并授权

-- 删除用户 user_demo
declare
  v_rowcount integer;
begin
  select count(*) into v_rowcount from dual where exists(select * from all_users a where a.username = upper('user_demo'));
  if v_rowcount > 0 then
    execute immediate 'DROP USER USER_DEMO CASCADE';
  end if;
end;
/
-- 创建用户 user_demo设置密码为123456，默认表空间为DATA_DEMO
CREATE USER USER_DEMO IDENTIFIED BY 123456 DEFAULT TABLESPACE DATA_DEMO TEMPORARY TABLESPACE TEMP;
-- 用户 user_demo 赋权限
-- 授权表空间DATA_DEMO的权限
ALTER USER user_demo QUOTA UNLIMITED ON DATA_DEMO;
GRANT CONNECT TO USER_DEMO;
GRANT RESOURCE TO USER_DEMO;

-- 授权 增删改查的权限及job权限
GRANT update any table TO USER_DEMO;
GRANT select any table TO USER_DEMO;
GRANT insert any table TO USER_DEMO;
GRANT delete any table TO USER_DEMO;
GRANT create any table TO USER_DEMO;
GRANT create any index to USER_DEMO;
GRANT alter any index to USER_DEMO;
GRANT drop any index to USER_DEMO;
GRANT create any job to USER_DEMO;

3.2、删除用户

--删除表空间
DROP TABLESPACE DATA_DEMO INCLUDING CONTENTS AND DATAFILES;
--删除用户
DROP USER USER_DEMO CASCADE;

4、报错及解决

4.1、ORA-01940: 无法删除当前连接的用户

通过查询用户session可能会返回多行记录，所以要多次执行alter语句

-- 查看用户session
select username,sid,serial# from V$SESSION where username ='USER_DEMO';
-- 立即断开用户session,未完成的事务自动回滚
alter system disconnect session 'sid,serial#' immediate;
-- 事务断开用户session，等待未完成的事务提交后，断开连接(推荐使用)
alter system disconnect session 'sid,serial#' post_transaction;