IBM Tivoli Composite Application Manager for Application Diagnostics, Version 7.1.0.1

---

Sample SQL script for creating flexible table space in Oracle

Instead of setting permanently fixed sizes for the table space and temporary data file, the following sample script uses AUTOEXTEND to enlarge these items as needed. It autoextends the table space (named $username) 5 MB at a time and the temporary data file 100 KB at a time.

#!/bin/ksh

if [ $# -ne 1 ]
then
    echo "Usage: create_users.sh username"
        exit 1
fi

username=$1

echo "Creating SQL file: /dbs1/create/"$username"_create.sql"

touch /dbs1/create/"$username"_create.sql
REM
REM Execute the following SQL fragment as Oracle RDBMS user "sys":
REM

create tablespace ts_$username
  datafile '/dbs1/ts_$username.dbf' size 30M
  default storage (
  initial 100K
  next 50K
  minextents 2
  maxextents 120)
  offline;

  commit;

create tablespace tmp_ts_$username
  datafile '/dbs1/tmp_ts_$username.dbf' size 3M
  default storage (
  initial 100K
  next 50K
  minextents 2
  maxextents 20)
  TEMPORARY
  offline;

  commit;

alter tablespace ts_$username online;
  commit;

alter tablespace tmp_ts_$username online;

  commit;


create profile profile_$username limit
  sessions_per_user 10
  cpu_per_session unlimited
  cpu_per_call 6000
  logical_reads_per_session unlimited
  logical_reads_per_call 100
  idle_time 30
  connect_time 480;

  commit;

create user $username
  identified by $username
  default tablespace ts_$username
  temporary tablespace tmp_ts_$username
  quota unlimited on ts_$username
  profile profile_$username;

  commit;

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO
  $username identified by $username;

  commit;

ALTER DATABASE DATAFILE '/dbs1/ts_$username.dbf' AUTOEXTEND ON NEXT 5M;

commit;

ALTER DATABASE DATAFILE '/dbs1/tmp_ts_$username.dbf' AUTOEXTEND ON NEXT 100K;

commit;

Parent topic:

Sample script for configuring existing databases

---

+

Search Tips   |   Advanced Search