Rem
Rem $Header: admin_create_tablespaces.sql 07-sep-2005.07:00:05 asaraswa Exp $
Rem
Rem admin_create_tablespaces.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_create_tablespaces.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    asaraswa    09/07/05 - rolling back creation of CSA tablespace 
Rem    asaraswa    05/04/05 - adding CSA tablespace 
Rem    rpinnama    02/25/03 - Pass the data files are arguments
Rem    edemembe    08/15/02 - Enable auto-segment space management
Rem    rpinnama    06/03/02 - rpinnama_reorg_rep_scripts_3
Rem    rpinnama    05/17/02 - Created
Rem

Rem
Rem Requires the following SQL*PLUS arguments to be sent.
Rem  1. Default table space name for the repos user
Rem  2. Datafile to be used for default tablespace
Rem  3. Datafile size of the datafile used for default tablespace
Rem  4. Datafile extend size of the datafile used for default tablespace
Rem  5. ECM depot table space name
Rem  6. Datafile to be used for ECM depot table space
Rem  7. Datafile size of the datafile to be used for ECM depot tablespace
Rem  8. Datafile extend size of the datafile to be used for ECM depot tablespace
Rem

define EM_DEFAULT_TABLESPACE_NAME     = "&1"
define EM_DEFAULT_DATAFILE_NAME       = "&2"
define EM_DEFAULT_DATAFILE_SIZE       = "&3"
define EM_DEFAULT_DATAFILE_EXTD_SIZE  = "&4"
define EM_ECM_DEPOT_TABLESPACE_NAME   = "&5"
define EM_ECM_DEPOT_DATAFILE_NAME     = "&6"
define EM_ECM_DEPOT_DATAFILE_SIZE     = "&7"
define EM_ECM_DEPOT_DATAFILE_EXT_SIZE = "&8"
define EM_ECM_CSA_TABLESPACE_NAME   = "&9"
define EM_ECM_CSA_DATAFILE_NAME     = "&10"
define EM_ECM_CSA_DATAFILE_SIZE     = "&11"
define EM_ECM_CSA_DATAFILE_EXT_SIZE = "&12"


--
--  Create tablespaces, if they dont exist.
--

DECLARE
    l_obj_name VARCHAR2(30);
    l_obj_exists NUMBER;
BEGIN
    l_obj_exists := 0;
 
    --
    -- Create default table space for repository user.
    -- Check if MGMT_TABLESPACE already exists.
    --
    BEGIN 
        l_obj_name := '';
        l_obj_exists := 0;
        SELECT tablespace_name INTO l_obj_name
          FROM dba_tablespaces 
         WHERE tablespace_name=upper('&EM_DEFAULT_TABLESPACE_NAME');

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If tablespace doesn't exist, create one..
    IF (l_obj_exists = 0) THEN
        BEGIN
	    EXECUTE IMMEDIATE 'CREATE TABLESPACE &EM_DEFAULT_TABLESPACE_NAME ' ||
                              ' DATAFILE ''' || '&EM_DEFAULT_DATAFILE_NAME' || '''' ||
                              ' SIZE &EM_DEFAULT_DATAFILE_SIZE ' ||
                              ' REUSE ' ||
                              ' AUTOEXTEND ON ' ||
                              ' NEXT &EM_DEFAULT_DATAFILE_EXTD_SIZE ' ||
                              ' MAXSIZE UNLIMITED ' ||
                              ' EXTENT MANAGEMENT LOCAL AUTOALLOCATE ' ||
                              ' SEGMENT SPACE MANAGEMENT AUTO ' ||
                              ' PERMANENT ONLINE';
	EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' &EM_DEFAULT_TABLESPACE_NAME. ');
            RAISE;
	END;
    END IF;


    --
    -- Create tablespace for storing ecm depot files.  
    -- These files can get quite large.
    --
    BEGIN 
        l_obj_name := '';
        l_obj_exists := 0;
        SELECT tablespace_name INTO l_obj_name
          FROM dba_tablespaces 
         WHERE tablespace_name=upper('&EM_ECM_DEPOT_TABLESPACE_NAME');

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If tablespace doesn't exist, create one..
    IF (l_obj_exists = 0) THEN
        BEGIN
	    EXECUTE IMMEDIATE 'CREATE TABLESPACE &EM_ECM_DEPOT_TABLESPACE_NAME ' ||
                              ' DATAFILE ''' || '&EM_ECM_DEPOT_DATAFILE_NAME' || '''' ||
                              ' SIZE &EM_ECM_DEPOT_DATAFILE_SIZE ' ||
                              ' REUSE ' ||
                              ' AUTOEXTEND ON ' ||
                              ' NEXT &EM_ECM_DEPOT_DATAFILE_EXT_SIZE ' ||
                              ' MAXSIZE UNLIMITED ' ||
                              ' EXTENT MANAGEMENT LOCAL AUTOALLOCATE ' ||
                              ' PERMANENT ONLINE';
	EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' &EM_ECM_DEPOT_TABLESPACE_NAME. ');
            RAISE;
	END;
    END IF;

/*
    --
    -- Create tablespace for storing CSA snapshots
    -- An individual snapshot should only take up a few KB of space,
    -- but we may have a very large number of snapshots
    BEGIN 
        l_obj_name := '';
        l_obj_exists := 0;
        SELECT tablespace_name INTO l_obj_name
          FROM dba_tablespaces 
         WHERE tablespace_name=upper('&EM_ECM_CSA_TABLESPACE_NAME');

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If tablespace doesn't exist, create one..
    IF (l_obj_exists = 0) THEN
        BEGIN
	    EXECUTE IMMEDIATE 'CREATE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME ' ||
                              ' DATAFILE ''' || '&EM_ECM_CSA_DATAFILE_NAME' || '''' ||
                              ' SIZE &EM_ECM_CSA_DATAFILE_SIZE ' ||
                              ' REUSE ' ||
                              ' AUTOEXTEND ON ' ||
                              ' NEXT &EM_ECM_CSA_DATAFILE_EXT_SIZE ' ||
                              ' MAXSIZE UNLIMITED ' ||
                              ' EXTENT MANAGEMENT LOCAL AUTOALLOCATE ' ||
                              ' PERMANENT ONLINE';
	EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' &EM_ECM_CSA_TABLESPACE_NAME. ');
            RAISE;
	END;
    END IF;
*/



END;
/