oracle 删除和重建表空间脚本 博客分类： 数据库 oraclesql

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

/**

清除原有表空间
重建表空间和用户
**/


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  ;

查询表空间文件
select * from dba_data_files


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';