Stuart at 18:04, 13 November 2013

2013-11-13T18:04:32Z

Stuart: Created page with "

 set    echo off  column datum new_value datum; select to_char(sysdate,'YYYYMMDD') datum from dual;  column oracleid new_value oracleid; select instance_name oracleid fro..."

2013-11-13T17:39:55Z

Created page with "<pre> set echo off column datum new_value datum; select to_char(sysdate,'YYYYMMDD') datum from dual; column oracleid new_value oracleid; select instance_name oracleid fro..."

New page

<pre>
set echo off

column datum new_value datum;
select to_char(sysdate,'YYYYMMDD') datum from dual;

column oracleid new_value oracleid;
select instance_name oracleid from v$instance;

-- -----------------------------------------------------------------------------
-- Check to what filesystem the TBS goes *_DT=oradata04 *_IX=oradata05
--
-- To run, type sqlplus "/ as sysdba" @create_user <schema_name> <schema_password>
-- -----------------------------------------------------------------------------

def oracleschema=&1
def oraclepasswd=&2

prompt "Are these details correct?"
prompt "=========================="
prompt "Oracle SID => " &&oracleid
prompt "oracleschema => " &&oracleschema
prompt "oraclepasswd => " &&oraclepasswd.
pause Press cntrl-d if not correct

set echo on

spool create_user_&&oracleschema._&&datum..log

-- ------------------------------------------------------------------
-- create permanent and temp tablespaces (if they dont exist already)
-- ------------------------------------------------------------------
create tablespace &oracleschema._DT datafile '/oracle/&oracleid./oradata4/&oracleschema._DT01.dbf' size 100M
autoextend on next 100M maxsize 20000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create tablespace &oracleschema._IX datafile '/oracle/&oracleid./oradata5/&oracleschema._IX01.dbf' size 100M
autoextend on next 100M maxsize 20000M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

--create BIGFILE tablespace WMDATA datafile '/oracle/&oracleid./oradata4/wmdata.dbf' size 100M autoextend on next 100M;
--create BIGFILE tablespace WMINDX datafile '/oracle/&oracleid./oradata5/wmindx.dbf' size 100M autoextend on next 100M;

-- ---------------
-- create the user
-- ---------------
create user &oracleschema identified by rubbish password expire profile oracle_schema_user default tablespace &oracleschema._DT temporary tablespace TEMP;
alter user &oracleschema identified by &oraclepasswd;
alter user &oracleschema. quota unlimited on WMDATA;
alter user &oracleschema. quota unlimited on WMINDX;
alter user &oracleschema. default profile all;

-- ------------------------------------------
-- grants to create objects in its own schema
-- ------------------------------------------
grant connect to &oracleschema
/
@/home/ibmtools/scripts/oracle/create_role_ibm_create_objects.sql

spool create_user_&&oracleschema._&&datum..log append

grant ibm_create_objects to &oracleschema.
/

-- ----------
-- validation
-- ----------
connect &oracleschema./&oraclepasswd.
create table dummy_test (name varchar2(10));
insert into dummy_test values ('wim');
insert into dummy_test select * from dummy_test;
/
/
/
drop table dummy_test;

spool off
exit;

-- drop tablespace &&oracleschema._DT including contents and datafiles;
-- drop tablespace &&oracleschema._IX including contents and datafiles;
-- drop user &&oracleschema. cascade;

</pre>

Create user with its own tablespaces - Revision history

Stuart at 18:04, 13 November 2013

Stuart: Created page with "
set echo off column datum new_value datum; select to_char(sysdate,'YYYYMMDD') datum from dual; column oracleid new_value oracleid; select instance_name oracleid fro..."

@@ Line 47: / Line 47: @@
 create user &oracleschema identified by rubbish password expire profile oracle_schema_user default tablespace &oracleschema._DT temporary tablespace TEMP;
 alter  user &oracleschema identified by &oraclepasswd;
-alter  user &oracleschema. quota unlimited on WMDATA;
+alter  user &oracleschema. quota unlimited on &oracleschema._DT;
-alter  user &oracleschema. quota unlimited on WMINDX;
+alter  user &oracleschema. quota unlimited on &oracleschema._IX;
-alter user  &oracleschema. default profile all;
+alter user  &oracleschema. default role all;