创建表空间自动化脚本

/*====================================================================/
  ##  功能描述：
  ##   创建数据库用户脚本
  ##   默认创建用户为：username/password
  ##   username=username
  ##   password=password
  ##   schema=username(数据表空间)
  ##  设 计 者：michales
  ##  设计日期: 2010-11-18
 
  ##  更 新 人: /
  ##  更新内容: /
  ##  更新日期: /
 /====================================================================*/
REM
REM Create a tablespace and username user
REM username/password
REM

SET ECHO OFF

connect sys/&sys_passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&_host)(PORT=&_port)))(CONNECT_DATA=(SERVICE_NAME=&_instance))) as sysdba

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

REM Create username tablespace
REM Oracle database requires that the datafile be qualified
REM explicitly. This procedure checks where the SYSTEM datafiles
REM are and then uses that directory for the username datafile.
REM If the username tablespace already exists, then do nothing.

SET ECHO OFF

DECLARE
  unix    NUMBER;
  win     NUMBER;
  tsexists NUMBER;
  uname   varchar2(512);
  wname   varchar2(512);
  datadir varchar2(512);
BEGIN

--
-- Check if tablespace already exists
--

select count(*)
    into tsexists from v$tablespace
    where name = 'username';

IF tsexists >= 1 THEN
    dbms_output.put_line('Tablespace already exists.');
    RETURN;
END IF;

--
-- We could either be on Windows or Unix. Search for the last
-- character that's either a / or \ and figure out which platform
-- we're on.
--

select instr(name, '\', -1), name
    into win, wname from v$datafile
    where file# = (select min(file#) from v$datafile where
          ts# = (select ts# from v$tablespace where name = 'SYSTEM'));

select instr(name, '/', -1), name
    into unix, uname from v$datafile
    where file# = (select min(file#) from v$datafile where
          ts# = (select ts# from v$tablespace where name = 'SYSTEM'));

IF unix > win THEN
    datadir := SUBSTR (uname, 1, unix);
ELSE
    datadir := SUBSTR (wname, 1, win);
END IF;

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

--
-- Create Tablespaces with datafiles in datadir.
--

execute immediate 'create tablespace username datafile ' ||
    '''' || datadir || 'username.dbf' || '''' ||
    ' SIZE 100M reuse autoextend on NEXT 30M maxsize unlimited';

END;
/

SET ECHO ON

REM
REM Create and grant privileges to username user
REM

drop user username cascade;

REM Create and assign privileges to username user
create user username identified by password
        default tablespace username;

GRANT CONNECT, RESOURCE TO username;
GRANT SELECT ANY TABLE TO username;
GRANT ALTER SESSION TO username;
GRANT ALTER DATABASE TO username;
GRANT CREATE ANY INDEX TO username;
GRANT CREATE SESSION TO username;
GRANT CREATE SYNONYM TO username;
GRANT CREATE VIEW TO username;
GRANT EXECUTE ANY PROCEDURE TO username;
GRANT IMP_FULL_DATABASE TO username;
GRANT DEBUG CONNECT SESSION TO username;