oracle 表空间重建,oracle 剔除和重建表空间脚本

oracle 删除和重建表空间脚本

调试数据库生成脚本，需要频繁重建表空间

/**

清除原有表空间

重建表空间和用户

**/

declare

tbs varchar2(100):='TS_data'; --表空间名称

tbs_tpm varchar2(100):='data_TEMP';--临时表空间名称

uname varchar2(100):='user';--用户名 密码为用户名小写

file_sp varchar2(100):='/';--文件分隔附 自动判断

tbs_exists INTEGER;

filepath varchar2(100);

dyn_sql varchar2(1000);

begin

--check exist

select count(*) INTO tbs_exists from dba_data_files where tablespace_name=tbs;

dbms_output.put_line(tbs||' exists '|| tbs_exists);

/** **/

--drop old table space

if tbs_exists>0 then

dbms_output.put_line('drop exists old table space '||tbs);

dyn_sql:='DROP USER "'||uname||'" cascade';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='DROP tablespace '||tbs||' including contents and datafiles cascade constraints ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='DROP tablespace '||tbs_tpm||' including contents and datafiles ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

end if;

-- windows 系统的文件分隔符

if(instr(dbms_utility.port_string,'WIN')>0) then

file_sp:='\';

end if;

-- init file path

select substr(file_name,0,instr(file_name,file_sp,-1,1)) into filepath from dba_data_files

where rownum=1;

dbms_output.put_line('filepath='||filepath);

--create new table space

dyn_sql:='create tablespace '||tbs||' logging datafile '''||filepath||tbs||'_data.dbf'' size 50m autoextend on next 10m maxsize unlimited ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='create temporary tablespace '||tbs_tpm||' tempfile '''||filepath||tbs_tpm||'.dbf'' size 500m autoextend on next 50m maxsize 2048m extent management local ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

-- USER SQL

dyn_sql:='create user '||uname||' identified by "'||lower(uname)||'" ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='ALTER USER '||uname||' DEFAULT TABLESPACE "'||tbs||'" TEMPORARY TABLESPACE "'||tbs_tpm||'" ACCOUNT UNLOCK ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='ALTER USER '||uname||' QUOTA UNLIMITED ON "'||tbs||'"';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='grant connect,resource,dba to '||uname||' ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='ALTER USER '||uname||' DEFAULT ROLE "DBA"';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

dyn_sql:='grant connect,resource,dba to '||uname||' ';

dbms_output.put_line(dyn_sql);

execute immediate dyn_sql;

end;

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

SQL>alter user XXX account lock;

SQL>SELECT * FROM V$SESSION WHERE USERNAME='LGDB'；

SQL>alter system kill session 'xx,xx'

SQL>drop user xx cascade

drop user and table space

DROP USER XXXXcascade;

DROP tablespace TSXXXXN including contents and datafiles cascade constraints ;

DROP tablespace XXXX_TEMP including contents and datafiles ;

create tablespace TS_XXX logging datafile '/opt/oracle/oradata/orcl_pdm/TS_XXXX_data.dbf' size 50m autoextend on next 10m maxsize unlimited;

create temporary tablespace XXXX_TEMP tempfile '/opt/oracle/oradata/orcl_pdm/XXXX_TEMP.dbf' size 500m autoextend on next 50m maxsize 2048m extent management local;

-- USER SQL

create user "XXXX" identified by "XXXX" ;

ALTER USER "XXXX"

DEFAULT TABLESPACE "TS_XXXX"

TEMPORARY TABLESPACE "XXXX_TEMP"

ACCOUNT UNLOCK ;

-- QUOTAS

ALTER USER "XXXX" QUOTA UNLIMITED ON TS_XXXX;

-- SYSTEM PRIVILEGES

grant connect,resource,dba to "XXXX" ;

-- ROLES

ALTER USER "XXXX" DEFAULT ROLE "DBA";

-- SYSTEM PRIVILEGES

grant connect,resource,dba to "XXXX" ;

select COUNT(*) from all_tables t where t.owner='XXXX';