Database Creation 12.1.0.1.0 using template Script and above
There are 3 basic scripts to create database using dbca
Please find details below list
Copy and paste below script on location :
u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates
dbca
Select Templete
- Template_Oracle.dbt
- tc_create_user_ilog.sql
- create_user.sql
##################### Template_Oracle.dbt
####################
<DatabaseTemplate name="tc" description="" version="12.1.0.1.0">
<CommonAttributes>
<option name="ISEARCH" value="false"/>
<option name="OMS" value="false"/>
<option name="JSERVER" value="false"/>
<option name="SPATIAL" value="false"/>
<option name="ODM" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="IMEDIA" value="false"/>
<option name="XDB_PROTOCOLS" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="ORACLE_TEXT" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="SAMPLE_SCHEMA" value="false"/>
<option name="CWMLITE" value="false">
<tablespace id="SYSAUX"/>
</option>
<option name="EM_REPOSITORY" value="true">
<tablespace id="SYSAUX"/>
</option>
</CommonAttributes>
<Variables/>
<CustomScripts Execute="true">
<CustomScript script="{ORACLE_HOME}/assistants/dbca/templates/tc_create_user_ilog.sql"/>
</CustomScripts>
<InitParamAttributes>
<InitParams>
<initParam name="control_files" value="("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control02.ctl", "{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control03.ctl")"/>
<initParam name="db_domain" value=""/>
<initParam name="db_file_multiblock_read_count" value="32"/>
<initParam name="db_name" value="tc"/>
<initParam name="instance_name" value="tc"/>
<initParam name="db_recovery_file_dest" value="{ORACLE_BASE}/flash_recovery_area"/>
<initParam name="db_recovery_file_dest_size" value="4096" unit="MB"/>
<initParam name="open_cursors" value="300"/>
<initParam name="pga_aggregate_target" value="500" unit="MB"/>
<initParam name="processes" value="150"/>
<initParam name="undo_tablespace" value="UNDOTBS1"/>
<initParam name="query_rewrite_integrity" value="TRUSTED"/>
<initParam name="sga_max_size" value="2048" unit="MB"/>
<initParam name="sga_target" value="2048" unit="MB"/>
<initParam name="optimizer_index_caching" value="95"/>
<initParam name="optimizer_index_cost_adj" value="10"/>
</InitParams>
<MiscParams>
<databaseType>MULTIPURPOSE</databaseType>
<maxUserConn>20</maxUserConn>
<percentageMemTOSGA>40</percentageMemTOSGA>
<customSGA>true</customSGA>
<archiveLogMode>false</archiveLogMode>
<initParamFileName>{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/pfile/init.ora</initParamFileName>
</MiscParams>
<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
</InitParamAttributes>
<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>100</maxDatafiles>
<maxLogfiles>16</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>1</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control02.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
<image name="control03.ctl" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</ControlfileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/idata01.dbf">
<tablespace>IDATA</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">500</size>
<reuse>false</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="KB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/ilog01.dbf">
<tablespace>ILOG</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">50</size>
<reuse>false</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="KB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/indx01.dbf">
<tablespace>INDX</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">50</size>
<reuse>false</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="KB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf">
<tablespace>SYSAUX</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">120</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf">
<tablespace>SYSTEM</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">400</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf">
<tablespace>TEMP</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">50</size>
<reuse>true</reuse>
<autoExtend>false</autoExtend>
<increment unit="KB">640</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf">
<tablespace>UNDOTBS1</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">500</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<TablespaceAttributes id="IDATA">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">-1</initSize>
<increment unit="KB">-1</increment>
<incrementPercent>1</incrementPercent>
<minExtends>-1</minExtends>
<maxExtends>-2</maxExtends>
<minExtendsSize unit="KB">-1</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/idata01.dbf">
<id>1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="ILOG">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">-1</initSize>
<increment unit="KB">-1</increment>
<incrementPercent>1</incrementPercent>
<minExtends>-1</minExtends>
<maxExtends>-2</maxExtends>
<minExtendsSize unit="KB">-1</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/ilog01.dbf">
<id>1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="INDX">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">-1</initSize>
<increment unit="KB">-1</increment>
<incrementPercent>1</incrementPercent>
<minExtends>-1</minExtends>
<maxExtends>-2</maxExtends>
<minExtendsSize unit="KB">-1</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/indx01.dbf">
<id>1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="SYSAUX">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>50</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/sysaux01.dbf">
<id>-1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="SYSTEM">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>3</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>50</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>-1</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/system01.dbf">
<id>-1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="TEMP">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>true</temporary>
<defaultTemp>true</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>0</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>0</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/temp01.dbf">
<id>-1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="UNDOTBS1">
<online>true</online>
<offlineMode>1</offlineMode>
<readOnly>false</readOnly>
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>true</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">512</initSize>
<increment unit="KB">512</increment>
<incrementPercent>50</incrementPercent>
<minExtends>8</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">512</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<datafilesList>
<TablespaceDatafileAttributes id="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/undotbs01.dbf">
<id>-1</id>
</TablespaceDatafileAttributes>
</datafilesList>
</TablespaceAttributes>
<RedoLogGroupAttributes id="1">
<reuse>false</reuse>
<fileSize unit="KB">102400</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo01.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="2">
<reuse>false</reuse>
<fileSize unit="KB">102400</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo02.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="3">
<reuse>false</reuse>
<fileSize unit="KB">102400</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo03.log" filepath="{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/"/>
</RedoLogGroupAttributes>
</StorageAttributes>
</DatabaseTemplate>
################# tc_create_user_ilog.sql
##################
REM EDS PLM
REM
REM File: tc_create_user_ilog.sql
REM
REM ****************************************************************************
REM Revision History:
REM Date Author Comment
REM ========= ====== =================================================
REM
REM ****************************************************************************
REM This script:
REM 1) creates the Tc test user account
REM 2) creates the Tc test logging tables/indexes
spool tc_create_user_ilog.lst
connect / as sysdba
REM create the Tc test account
@@create_user.sql
################# create_user.sql #######################
REM EDS PLM
REM
REM File: create_user.sql
REM
REM ****************************************************************************
REM Revision History:
REM Date Author Comment
REM ========= ====== =================================================
REM 12-Mar-03 Initial.
REM 14-Jul-05 Remove dba privilege;only grant those required.
REM 11-Jan-06 Give test imp_full_database role.
REM
REM ****************************************************************************
REM This script creates the TcEng test account.
REM This script expects 2 existing tablespaces: "idata" and "temp".
prompt Creating test account and granting privileges.
grant Connect, Create table, Create procedure, Create view, Select_catalog_role, alter session to test identified by test;
prompt Setting default tablespaces for the test account.
alter user test default tablespace idata temporary tablespace temp;
ALTER USER "TEST" QUOTA UNLIMITED ON "IDATA" QUOTA UNLIMITED ON "ILOG" QUOTA UNLIMITED ON "INDX";
spool off
exit
