Forum OpenACS Development: Re: AOLServer 4.0 hang on shutdown fixed

Collapse
Posted by Jeff Davis on
Mat, I guess you are sort of joking about the oracle
bit, but it might be nice to ship an oracle-db-create
or something like that which prompts for the server name
and tablespace location and writes a service-create.sql and service-drop.sql script to feed to svrmgrl.

Lars has a page to write the necessary oracle statements here:
http://pinds.com/acs-tips/oracle-statements

Collapse
Posted by Tilmann Singer on
Something like that?
#!/bin/sh
#
# /usr/local/bin/create-tablespace.tcl
#
#
#
# the next line restarts using tclsh \
exec tclsh "$0" "$@"

if { !($argc == 1 || $argc == 2) } {
    puts "Usage:

./create-tablespace.tcl service_name \[database_password\]

This will output the sql statements needed to create the tablespace.
If you don't specify database_password then the service name will be used as password.

You have to copy and paste the statements into sqlplus.
"
    exit
}


set service_name [lindex $argv 0]
if { $argc == 1 } {
    # default pwd
    set database_password "${service_name}"
} else {
    # pwd specified
    set database_password [lindex $argv 1]
}

puts " \

spool create-${service_name}.log

    # default pwd
    set database_password "${service_name}"
} else {
    # pwd specified
    set database_password [lindex $argv 1]
}

puts " \

spool create-${service_name}.log

REM * Start the instance (ORACLE_SID must be set).
REM * We expect the database to already be started.
REM *

connect internal
drop user ${service_name} cascade;
drop tablespace ${service_name} including contents;

REM * Create user and tablespace for live site
REM *
create tablespace ${service_name}
 datafile '/ora8/m01/app/oracle/oradata/ora8/${service_name}01.dbf' size 50m autoexte
nd on next 640k maxsize 2147450880 extent management local uniform size 160K;
REM *

connect internal
drop user ${service_name} cascade;
drop tablespace ${service_name} including contents;

REM * Create user and tablespace for live site
REM *
create tablespace ${service_name}
 datafile '/ora8/m01/app/oracle/oradata/ora8/${service_name}01.dbf' size 50m autoexte
nd on next 640k maxsize 2147450880 extent management local uniform size 160K;

create user ${service_name} identified by ${database_password}
  default tablespace ${service_name}
  temporary tablespace temp
  quota unlimited on ${service_name};


grant ctxapp, javasyspriv, connect, resource, query rewrite to ${service_name};

grant create table, select any table, create materialized view, connect, resource, qu
ery rewrite to ${service_name};

revoke unlimited tablespace from ${service_name};
  default tablespace ${service_name}
  temporary tablespace temp
  quota unlimited on ${service_name};


grant ctxapp, javasyspriv, connect, resource, query rewrite to ${service_name};

grant create table, select any table, create materialized view, connect, resource, qu
ery rewrite to ${service_name};

revoke unlimited tablespace from ${service_name};

alter user ${service_name} quota unlimited on ${service_name};

REM * Allow user to use autotrace
connect ${service_name}/${database_password}
@/ora8/m01/app/oracle/product/8.1.7/rdbms/admin/utlxplan.sql

REM * All done, so close the log file and exit.
REM *
spool off
exit

"
If no one objects I can commit that to openacs/bin/. Or am I missing the joke here ;) ?