Linux script file to create database in Oracle

To create oracle database, user and tablespace in linux, use the below script file. Please go through the script file carefullly before executing or creating the database, as the values of sga_target and pga_aggregate_target are provided very less


kasi.sh : 

cd $ORACLE_HOME
ORACLEHOME=$ORACLE_HOME
cd ../../..
cd oradata
echo "Please enter the database name"
read name
mkdir $name
cd $name
dir_path=`pwd`;
echo "YOUR FILES WILL BE STORED ON THIS DIRECTORY: $dir_path"

cat > 1.sql <<EOF
startup pfile='$dir_path/init$name.ora' nomount;
create spfile from pfile='$dir_path/init$name.ora';
EOF

cat > init$name.ora <<EOF
pga_aggregate_target=922746880
sga_target=2751463424
compatible='11.2.0.0.0'
control_files='$dir_path/control01.ctl','$dir_path/control02.ctl'
db_block_size=8192
db_name='$name'
diagnostic_dest='$ORACLE_BASE/diag'
processes=600
undo_tablespace='undotbs'
open_cursors=5000
EOF

cat > create$name.sql <<EOF
CREATE DATABASE "$name"
LOGFILE
  GROUP 1 '$dir_path/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '$dir_path/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '$dir_path/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE '$dir_path/system01.dbf' size 500m REUSE
SYSAUX DATAFILE '$dir_path/sysaux01.dbf' SIZE 325M REUSE
UNDO TABLESPACE undotbs DATAFILE '$dir_path/undotbs01.dbf' SIZE 900M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TABLESPACE users DATAFILE '$dir_path/users01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '$dir_path/temptbs01.dbf' SIZE 200M REUSE;
EOF

cat > tablespace.sql << EOF
CREATE TABLESPACE DATA DATAFILE '$dir_path/DATA.DBF' SIZE 500M AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
EOF

cat > user.sql << EOF
CREATE USER user1 IDENTIFIED BY pass1 DEFAULT TABLESPACE DATA QUOTA UNLIMITED ON DATA;
grant connect to user1;
grant resource to user1;
grant query rewrite to user1;
grant create synonym to user1;
grant drop any procedure to user1;
grant create any table to user1;
grant select any table to user1;
grant update any table to user1;
grant insert any table to user1;
grant delete any table to user1;
grant drop any table to user1;
grant create any index to user1;
grant alter any index to user1;
grant drop any index to user1;
grant create any view to user1;
grant drop any view to user1;
grant create any directory to user1;
grant exp_full_database to user1;
grant imp_full_database to user1;
@?/rdbms/admin/catblock.sql;
grANT EXECUTE ON DBMS_LOCK TO user1;
EOF

export $ORACLE_SID=$name;
sqlplus -s /nolog << EOF
CONNECT / as sysdba;
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
@1.sql;
@create$name.sql;
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
conn system/manager;
@?/sqlplus/admin/pupbld.sql;
startup force;
@tablespace.sql;
@user.sql;
shut immediate
EOF

DELETE RECORDS USING BULK COLLECT - ORACLE

Below is the procedure to delete records from table1 using bulk collect method. In the below example the limit is specified as 100, 000 records.


Pl/Sql :

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_del IS TABLE OF TABLE2%ROWTYPE;
l_tab t_bulk_del;
CURSOR bulk_del IS
SELECT RECID FROM TABLE2;
BEGIN
OPEN bulk_del;
LOOP
FETCH bulk_del
BULK COLLECT INTO l_tab LIMIT 100000;
forall vloop in 1 .. l_tab.count
delete from TABLE1 where recid = l_tab(vloop);
commit;
EXIT WHEN l_tab.count = 0;
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE bulk_del;
END;
/