Rem 
Rem File Name : ckcreate.sql
Rem
Rem This script creates the necessary Database objects required by the Repository.
Rem 
Rem Author    : Oracle Corporation UK
Rem 

spool ckcreate.log

set verify off

Rem *************** Create and grant the role CK_ORACLE_REPOS_OWNER for system privileges

prompt 
prompt Dropping role CK_ORACLE_REPOS_OWNER if it already exists 
DROP ROLE CK_ORACLE_REPOS_OWNER; 

prompt 
prompt Creating role CK_ORACLE_REPOS_OWNER 
CREATE ROLE CK_ORACLE_REPOS_OWNER; 

prompt 
prompt Granting privileges to CK_ORACLE_REPOS_OWNER 
GRANT ALTER SESSION 
, CREATE SESSION 
, CREATE ANY TABLE 
, CREATE ANY VIEW 
, CREATE ANY SNAPSHOT 
, CREATE ANY SYNONYM 
, CREATE ANY PROCEDURE 
, CREATE ANY SEQUENCE 
, CREATE ANY TRIGGER 
, CREATE ANY INDEX 
, CREATE ANY TYPE 
, CREATE ANY CLUSTER 
, CREATE ROLE 
, CREATE DATABASE LINK 
, DROP ANY SYNONYM 
, SELECT ANY SEQUENCE 
  TO CK_ORACLE_REPOS_OWNER ; 

prompt 
prompt Granting EXECUTE privilege on STANDARD to CK_ORACLE_REPOS_OWNER 
GRANT EXECUTE ON standard TO CK_ORACLE_REPOS_OWNER;

Rem     ******************* Begin creation of tablespace

set serveroutput on size 1000000
set feedback on
set verify off

prompt ************************************************
prompt *         Creating database Objects            *
prompt ************************************************

prompt 
prompt Creating tablespaces ..............
prompt

prompt
prompt Creating CONSTANT_GROW_INDEXES tablespace ....
create tablespace &TBS_NAME1
			datafile '&DF_NAME1' size &CONST_GROW_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating CONSTANT_GROW_TABLES tablespace ....
create tablespace &TBS_NAME2
			datafile '&DF_NAME2' size &CONST_GROW_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating DEPENDENCY_INDEXES tablespace ....
create tablespace &TBS_NAME3
			datafile '&DF_NAME3' size &DEP_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating DEPENDENCY_TABLES tablespace ....
create tablespace &TBS_NAME4
			datafile '&DF_NAME4' size &DEP_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating DIAGRAM_INDEXES tablespace ....
create tablespace &TBS_NAME5
			datafile '&DF_NAME5' size &DIAG_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating DIAGRAM_TABLES tablespace ....
create tablespace &TBS_NAME6
			datafile '&DF_NAME6' size &DIAG_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating LOB_DATA tablespace ....
create tablespace &TBS_NAME7
			datafile '&DF_NAME7' size &LOB_DATA_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating RAPID_GROW_INDEXES tablespace ....
create tablespace &TBS_NAME8
			datafile '&DF_NAME8' size &RAP_GROW_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating RAPID_GROW_TABLES tablespace ....
create tablespace &TBS_NAME9
			datafile '&DF_NAME9' size &RAP_GROW_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating SYSTEM_META_INDEXES tablespace ....
create tablespace &TBS_NAME10
			datafile '&DF_NAME10' size &SYS_META_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating SYSTEM_META_TABLES tablespace ....
create tablespace &TBS_NAME11
			datafile '&DF_NAME11' size &SYS_META_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating TEMPORARY_INDEXES tablespace ....
create tablespace &TBS_NAME12
			datafile '&DF_NAME12' size &TEMP_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating TEMPORARY_TABLES tablespace ....
create tablespace &TBS_NAME13
			datafile '&DF_NAME13' size &TEMP_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating VERSION_INDEXES tablespace ....
create tablespace &TBS_NAME14
			datafile '&DF_NAME14' size &VER_INDX_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

prompt
prompt Creating VERSION_TABLES tablespace ....
create tablespace &TBS_NAME15
			datafile '&DF_NAME15' size &VER_TAB_SIZE
      autoextend on next 100K 
default storage (initial &INITIAL_VALUE
		next &NEXT_VALUE
		minextents &MINEXT_VALUE
		maxextents &MAXEXT_VALUE
		pctincrease &PCTINC_VALUE
);

--prompt
--prompt Creating &RTBLSPACE tablespace ....
--create tablespace &RTBLSPACE
--			datafile '&RDFILENAME' size 10m;

--Bug 2559083: Replaced following statement for creating temporary tablespace 
--as dictionary managed with new syntax for creating it as locally managed.
--prompt
--prompt Creating &TEMPTBLSPACE tablespace ....
--create tablespace &TEMPTBLSPACE
--			datafile '&TEMPFILENAME' size &TEMPFILESIZE temporary;

prompt
prompt Creating &TEMPTBLSPACE tablespace ....
create temporary tablespace &TEMPTBLSPACE
			tempfile '&TEMPFILENAME' size &TEMPFILESIZE
      autoextend on next 100K ;


Rem ****************  Create Rollback segments and put it online
set serveroutput on

--
-- Starting Oracle9i onwards we need not create Rollback segments explicitly as the database
-- has an UNDO tablespace for UNDO information. However, there is a choice of creating a rollback 
-- segment explicitly by setting the 'UNDO_MANAGEMENT' parameter to MANUAL. So, when the database
-- is created with 'UNDO_MANAGEMENT' parameter set to MANUAL it means that there is no UNDO 
-- tablespace and hence we need to create the rollback tablespace and rollback segments.
-- 
declare
  l_mode       varchar2(30);
  l_plsql_str  varchar2(500);
  l_rbs_name   varchar2(40) := '&RBSNAME';
  l_rtbl_space varchar2(40) := '&RTBLSPACE';
  l_data_file  varchar2(1000) := '&RDFILENAME';
begin
  begin
    select UPPER(value)
    into   l_mode
    from   v$parameter
    where  UPPER(name) = 'UNDO_MANAGEMENT';
  exception
    when no_data_found then
	  l_mode := 'MANUAL'; -- This means it is an Oracle8i database.
  end;
  
  if l_mode = 'MANUAL' then
    dbms_output.put_line('Creating tablespace ' || l_rtbl_space );
	l_plsql_str := ' create tablespace ' || l_rtbl_space || ' datafile ' || '''' || l_data_file || '''' || ' size 10m';
	execute immediate l_plsql_str;
       
    dbms_output.put_line('Creating rollback segment ' || l_rbs_name );
	l_plsql_str := 'create rollback segment ' || l_rbs_name || ' tablespace ' || l_rtbl_space  ||
	               ' storage (initial 1m next 1m minextents 3 optimal 4m) ';
    execute immediate l_plsql_str;
	
	l_plsql_str := 'alter rollback segment ' || l_rbs_name || ' online';
	execute immediate l_plsql_str;
  end if;
end;
/

set serveroutput off
set verify off

prompt 
prompt End of tablespace creation process.

set pages 30
col tablespace_name for a25
set feedback off

prompt 
prompt ****************************************************
prompt Following tablespaces were created
prompt ****************************************************

select tablespace_name
       ,status
from   dba_tablespaces
where  tablespace_name in ('CONSTANT_GROW_INDEXES','CONSTANT_GROW_TABLES'
                          ,'DEPENDENCY_INDEXES'   ,'DEPENDENCY_TABLES'
                          ,'DIAGRAM_INDEXES'      ,'DIAGRAM_TABLES'
                          ,'LOB_DATA'             ,'RAPID_GROW_INDEXES'
                          ,'RAPID_GROW_TABLES'    ,'SYSTEM_META_INDEXES'
                          ,'SYSTEM_META_TABLES'   ,'TEMPORARY_INDEXES'
                          ,'TEMPORARY_TABLES'     ,'VERSION_INDEXES'
                          ,'VERSION_TABLES'       , upper('&RTBLSPACE')
                          ,upper('&TEMPTBLSPACE')
                          )
order by tablespace_name;

set pages 14
set feedback on
prompt 


--prompt Creating rollback segment &RBSNAME .....
--create rollback segment &RBSNAME tablespace &RTBLSPACE 
--	storage (initial 1m next 1m minextents 3 optimal 4m);

--prompt Bring the rollback segment online .....
--alter rollback segment &RBSNAME online;

set feedback off
prompt Name and Status of the rollback segment

select segment_name, status
from   dba_rollback_segs
where  segment_name in (upper('&RBSNAME'));

prompt 
prompt State of non-system rollback segments

select segment_name,status
from   dba_rollback_segs
where  tablespace_name not in ('SYSTEM');

set verify on

set serveroutput on

Rem *************** Check that SYSTEM tablespace has 140Mb of free space

prompt
prompt 
prompt The repository packages, procedures and views are stored in the SYSTEM tablespace, and needs 140Mb of free space
prompt ****************************************************************************************************************

col file_name format a40
col tablespace_name format a20

prompt 
prompt   Current datafile size(in MB) of SYSTEM tablespace
select file_name
	    ,tablespace_name
	    ,bytes/1024/1024
from 	 dba_data_files
where  tablespace_name = 'SYSTEM';

prompt 
prompt   Current Free space(in MB) available in SYSTEM tablespace
select tablespace_name
     , sum(bytes)/1024/1024 
from   dba_free_space
where  tablespace_name = 'SYSTEM'
group by tablespace_name;

prompt
Rem *************** Increase the size of data file for the SYSTEM tablespace

declare
 -- Cursor to pick the latest data file of SYSTEM tablespace.
 cursor cur_files is
  select file_name
        ,(bytes/1024/1024) currentsize
  from   dba_data_files
  where  tablespace_name = 'SYSTEM'
  and    rownum = 1
  order by file_id desc ;
 
 l_plsql_body varchar2(1000) ;
 l_currentsize   number(10,6);
 l_freespace   number(10,6);
 l_newsize   varchar2(20);
 
begin
  
  -- Determine the amount of free space
  select sum(bytes/1024/1024)
  into   l_freespace
  from   dba_free_space
  where  tablespace_name = 'SYSTEM'
  group by tablespace_name;
  
  if l_freespace < 140 then 
    for l_files in cur_files loop 
      -- Compute the new file size
      l_newsize := round(l_files.currentsize + (150 - l_freespace)) || 'M'; 
      
      l_plsql_body := 'alter database datafile ' || '''' || l_files.file_name || '''' || ' resize ' || l_newsize;
      execute immediate l_plsql_body;
    end loop;
    dbms_output.put_line('Datafile resizing complete .....');
  else
    dbms_output.put_line('Sufficient free space available. No resizing necessary ..');
  end if;
end;
/    

prompt 
prompt Free space(in MB) available in system tablespace ...
prompt ******************************************************

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

prompt 

set feedback on
set serveroutput off

set verify off

Rem *************** Create the Repository owner

prompt
prompt Creating Repository User : &REPOSOWNER having Password : MANAGER
prompt 
create user &REPOSOWNER identified by MANAGER
default tablespace &DEFTABSPACE temporary tablespace &TEMPTBLSPACE;

Rem *************** Grant privileges to Repository Owner

prompt
prompt Granting privilege on database objects to &REPOSOWNER
prompt *****************************************************

prompt
prompt Granting EXECUTE on dbms_rls to &REPOSOWNER ....
grant execute on dbms_rls to &REPOSOWNER;

prompt
prompt Granting EXECUTE on dbms_lock to &REPOSOWNER ....
grant execute on dbms_lock to &REPOSOWNER;

prompt
prompt Granting EXECUTE on dbms_pipe to &REPOSOWNER ....
grant execute on dbms_pipe to &REPOSOWNER;

prompt
prompt Granting CREATE TABLE to &REPOSOWNER ....
grant create table to &REPOSOWNER;

prompt
prompt Granting CREATE VIEW to &REPOSOWNER ....
grant create view to &REPOSOWNER;

prompt
prompt Granting CREATE PROCEDURE to &REPOSOWNER ....
grant create procedure to &REPOSOWNER;

prompt
prompt Granting CREATE SYNONYM to &REPOSOWNER ....
grant create synonym to &REPOSOWNER;

prompt
prompt Granting CREATE SEQUENCE to &REPOSOWNER ....
grant create sequence to &REPOSOWNER;

prompt
prompt Granting SELECT on sys.v_$nls_parameters to &REPOSOWNER ....
grant select on sys.v_$nls_parameters to &REPOSOWNER with grant option;

prompt
prompt Granting SELECT on sys.v_$parameter to &REPOSOWNER ....
grant select on sys.v_$parameter to &REPOSOWNER;

prompt
prompt Granting SELECT on dba_rollback_segs to &REPOSOWNER ....
grant select on dba_rollback_segs to &REPOSOWNER;

prompt
prompt Granting SELECT on dba_segments to &REPOSOWNER ....
grant select on dba_segments to &REPOSOWNER;

prompt
prompt Granting CREATE ANY SYNONYM to &REPOSOWNER ....
grant create any synonym to &REPOSOWNER;

prompt
prompt Granting DROP ANY SYNONYM to &REPOSOWNER ....
grant drop any synonym to &REPOSOWNER;

prompt
prompt Granting CK_ORACLE_REPOS_OWNER to &REPOSOWNER ....
grant ck_oracle_repos_owner to &REPOSOWNER;

prompt
prompt Granting CONNECT,RESOURCE to &REPOSOWNER ....
grant connect,resource to &REPOSOWNER;

prompt
prompt Granting ALL on CKRAU_TEMP to &REPOSOWNER ....
grant ALL on sys.CKRAU_TEMP to &REPOSOWNER;

Rem This synonym is being created for use by the ckreport.sql script
prompt
prompt Creating Synonym CKRAU_TEMP ....
create synonym &REPOSOWNER..ckrau_temp for sys.ckrau_temp;

Rem *************** Grant drop/create public synonym to Repository Owner.

declare
 l_plsql_body varchar2(1000) ;
begin
  if '&MOREUSERS' in ('Y','y') then
      l_plsql_body := 'grant create public synonym to &REPOSOWNER';
      execute immediate l_plsql_body;
      l_plsql_body := 'grant drop public synonym to &REPOSOWNER';
      execute immediate l_plsql_body;
      dbms_output.put_line(' Granted CREATE/DROP PUBLIC SYNONYM to &REPOSOWNER ');
  end if;
end;
/

prompt
prompt End of Grant privilege on database objects to &REPOSOWNER
prompt

prompt   End of 'Create database Objects'  process
prompt ************************************************
prompt 

set verify on

spool off
