oracle建库脚本

   把之前写的项目中的oracle数据库的建库sql脚本放在这里备份一下。

  

-- create_db.smb.orcl.sql
-- SHOULD EXECUTE WITH THE SUPPER USER 'sysdba'.
-- Before executing this script, should create a directory for the tablespace, 
-- then modify the LOCATION in line 26.

declare
      num   number;
begin 
      select count(1) into num from all_users where username = 'SMB';
      dbms_output.put_line(num);
      if   num=1   then 
          execute immediate 'DROP USER smb cascade';  
      end   if; 
end;
/
declare
      num   number;
begin 
      select count(1) into num from dba_tablespaces where TABLESPACE_NAME='SMB';
      dbms_output.put_line(num);
      if   num=1   then 
          execute immediate 'DROP TABLESPACE smb INCLUDING CONTENTS AND DATAFILES';  
      end   if; 
end;
/
CREATE TABLESPACE smb DATAFILE '/home/oracle/app/oradata/orcl/smb_table_space.dbf' size 200M AUTOEXTEND on next 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL ;
CREATE USER smb IDENTIFIED BY smb DEFAULT TABLESPACE smb ACCOUNT UNLOCK ;

--grant smb
GRANT CONNECT TO smb; 
GRANT RESOURCE TO smb;
GRANT CREATE SESSION TO smb;
GRANT UNLIMITED TABLESPACE TO smb;