prompt "This checks the current settings of the relevant init.ora parameters:"

column name format a30
column value format a20
column minimum format a20
select name, value, '8.1.6' Minimum from v$parameter
where name = 'compatible'
union all
select name, value, '30' Minimum from v$parameter
where name = 'max_enabled_roles'
union all
select name, value, '262144' Minimum from v$parameter
where name = 'sort_area_size'
union all
select name, value, '65536' Minimum from v$parameter
where name = 'sort_area_retained_size'
union all
select name, value, '1048576' Minimum from v$parameter
where name = 'hash_area_size'
union all
select name, value, '50' Minimum from v$parameter
where name = 'optimizer_index_caching'
union all
select name, value, '25' Minimum from v$parameter
where name = 'optimizer_index_cost_adj'
union all
select name, value, '32000000' Minimum from v$parameter
where name = 'shared_pool_size'
union all
select name, value, '2000' Minimum from v$parameter
where name = 'db_block_buffers'
union all
select name, value, '1000' Minimum from v$parameter
where name = 'open_cursors'
union all
select name, value, '100' Minimum from v$parameter
where name = 'processes'
union all
select name, value, '8/16/32' Minimum from v$parameter
where name = 'db_file_multiblock_read_count'
union all
select name, value, '8/4/2K' Minimum from v$parameter
where name = 'db_block_size';



prompt "This creates the 15 different tablespaces for the repository."
prompt "You might want to use only a single tablespace."

create tablespace constant_grow_indexes
datafile '/oracle/designer/cgrowi.dbf'
size 420K autoextend on next 200K maxsize 2400K
default storage (initial 100K next 100K minextents 1 maxextents unlimited pctincrease 0);

create tablespace constant_grow_tables
datafile '/oracle/designer/cgrowt.dbf'
size 1080K autoextend on next 500K maxsize 9000K
default storage (initial 100K next 100K minextents 1 maxextents unlimited pctincrease 0);

create tablespace dependency_indexes
datafile '/oracle/designer/depti.dbf'
size 600K autoextend on next 300K maxsize 3600K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace dependency_tables
datafile '/oracle/designer/deptt.dbf'
size 660K autoextend on next 300K maxsize 4800K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace diagram_indexes
datafile '/oracle/designer/diagi.dbf'
size 180K autoextend on next 100K maxsize 840K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace diagram_tables
datafile '/oracle/designer/diagt.dbf'
size 240K autoextend on next 150K maxsize 1320K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace lob_data
datafile '/oracle/designer/lobdata.dbf'
size 400K autoextend on next 150K maxsize 700K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace rapid_grow_indexes
datafile '/oracle/designer/rgrowi.dbf'
size 24000K autoextend on next 10000K maxsize 180000K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace rapid_grow_tables
datafile '/oracle/designer/rgrowt.dbf'
size 44600K autoextend on next 10000K maxsize 231500K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace system_meta_indexes
datafile '/oracle/designer/sysmetai.dbf'
size 13200K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace system_meta_tables
datafile '/oracle/designer/sysmetat.dbf'
size 18000K autoextend on next 1000K maxsize 21000M
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace temporary_indexes
datafile '/oracle/designer/tmpind.dbf'
size 3600K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace temporary_tables
datafile '/oracle/designer/tmptab.dbf'
size 6000K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace version_indexes
datafile '/oracle/designer/verind.dbf'
size 720K autoextend on next 1000K maxsize 5720K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);

create tablespace version_tables
datafile '/oracle/designer/vertab.dbf'
size 2800K autoextend on next 1000K maxsize 9800K
default storage (initial 100K next 100K minextents 1 maxextents unlimited
		pctincrease 0);




prompt "Next extent size of temporary tablespace must be at least 256K"

select tablespace_name, next_extent
from dba_tablespaces
where contents = 'TEMPORARY';

alter tablespace temp default storage(next 256K);



prompt "Now we make a large rollback segment for the installation"

create tablespace large_rollback
datafile '/oracle/designer/largerbs.dbf' size 10M;

create rollback segment lrbs tablespace large_rollback
storage(initial 1M next 1M minextents 3 optimal 4M);

alter rollback segment lrbs online;

select segment_name, status from dba_rollback_segs;

prompt "Copy and paste the output of the following query into SQL*Plus:"

select 'ALTER ROLLBACK SEGMENT ' || SEGMENT_NAME || ' OFFLINE;'
from dba_rollback_segs
where status = 'ONLINE'
and segment_name not in ('SYSTEM', 'LRBS');

alter rollback segment rbs0 offline;
alter rollback segment rbs1 offline;
alter rollback segment rbs2 offline;
alter rollback segment rbs3 offline;
alter rollback segment rbs4 offline;
alter rollback segment rbs5 offline;
alter rollback segment rbs6 offline;




prompt The SYSTEM tablespace must have at least 140M free space:

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'SYSTEM';

select autoextensible from dba_data_files where tablespace_name = 'SYSTEM';

select file_name, bytes/1024/1024
from dba_data_files where tablespace_name = 'SYSTEM';

alter database datafile '/oracle/designer/SYSTEM01.DBF' resize 380M;

select tablespace_name, status
from dba_tablespaces where contents = 'TEMPORARY';

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TEMP';





prompt "Now we make the repository user: "

create user repos identified by secret_pw
default tablespace rapid_grow_tables
temporary tablespace temp
quota unlimited on CONSTANT_GROW_INDEXES
quota unlimited on CONSTANT_GROW_TABLES
quota unlimited on DEPENDENCY_INDEXES
quota unlimited on DEPENDENCY_TABLES
quota unlimited on DIAGRAM_INDEXES
quota unlimited on DIAGRAM_TABLES
quota unlimited on LOB_DATA
quota unlimited on RAPID_GROW_INDEXES
quota unlimited on RAPID_GROW_TABLES
quota unlimited on SYSTEM_META_INDEXES
quota unlimited on SYSTEM_META_TABLES
quota unlimited on TEMPORARY_INDEXES
quota unlimited on TEMPORARY_TABLES
quota unlimited on VERSION_INDEXES
quota unlimited on VERSION_TABLES
quota 150M on SYSTEM
quota unlimited on TEMP;

@H:\orant\repadm61\utl\ckrorole

GRANT EXECUTE ON DBMS_LOCK TO repos;
GRANT EXECUTE ON DBMS_PIPE TO repos;
GRANT CREATE TABLE TO repos;
GRANT CREATE VIEW TO repos;
GRANT CREATE PROCEDURE TO repos;
GRANT CREATE SYNONYM TO repos;
GRANT CREATE SEQUENCE TO repos;
GRANT SELECT ON SYS.V_$NLS_PARAMETERS TO repos WITH GRANT OPTION;
GRANT SELECT ON SYS.V_$PARAMETER TO repos;
GRANT SELECT ON DBA_ROLLBACK_SEGS TO repos;
GRANT SELECT ON DBA_SEGMENTS TO repos;
GRANT CREATE ANY SYNONYM TO repos;
GRANT DROP ANY SYNONYM TO repos;

GRANT CREATE PUBLIC SYNONYM TO repos;
GRANT DROP PUBLIC SYNONYM TO repos;

grant ck_oracle_repos_owner to repos;

grant connect, resource to repos;