Oracle常用数据库操作SQL

• 新建表空间、用户、授权

-- 新建表空间
-- select * from dba_tablespaces;
create tablespace WEBSITE
datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\WEBSITE.MDF' size 50M autoextend on next 1M maxsize unlimited;
extent management local segment space management auto

-- 新建临时表空间
create temporary tablespace TEMP_WEBSITE
tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP_WEBSITE.MDF' size 50M autoextend on next 1M maxsize unlimited;

-- create user
create user WEBADMIN identified by passwords
default tablespace WEBSITE
quota unlimited on WEBSITE
TEMPORARY TABLESPACE "TEMP_WEBSITE"
account unlock;

grant connect,resource to webadmin;
grant select on v_$statname to webAdmin;
grant select on v_$sesstat to webAdmin;
grant select on v_$session to webAdmin;
grant select on v_$mystat to webAdmin;
grant unlimited tablespace to webAdmin with admin option;
alter user webAdmin quota unlimited on users

• 删除用户

-- 删除用户
-- drop user dzysc cascade;
-- select * from all_users;
declare
num number;
u_name varchar2(20);
begin
u_name := 'USER2';
SELECT count(1) into num FROM all_users where USERNAME = u_name;
if num> 0 then
dbms_output.put_line('DROP USER '|| u_name ||' CASCADE');--用来输出
execute immediate 'DROP USER '|| u_name ||' CASCADE';--执行删除
end if;
end;

用户会话无法终止时

select username,sid,serial# from v$session

将查询到的 sid 和 serial 填到下面 ，如
alter system kill session '136,33';
alter system kill session '202,1044';
drop user username cascade;
--cascade：连带删除此用户下的所有对象

• 删除表空间

-- del tablespace
declare
num number;
w_name varchar2(20);
-- url varchar2(100);
begin
w_name := 'WEBSITE';
--url := 'e:\oracle\product\10.2.0\oradata\orcl\WEBSITE.DBF';
SELECT count(1) into num FROM SYS.SM$TS_AVAIL A WHERE A.TABLESPACE_NAME=w_name;
if num> 0 then
dbms_output.put_line('DROP TABLESPACE '|| w_name ||' INCLUDING CONTENTS');--用来输出
execute immediate 'DROP TABLESPACE '|| w_name ||' INCLUDING CONTENTS AND DATAFILES';
end if;
end;