欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-15 11:57:53
...
调试数据库生成脚本,需要频繁重建表空间


/**

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


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

相关标签: oracle sql