指令碼建立表空間、使用者、表

文章版權所有 Jusin Hao（luckyfriends） ，支援原創，轉載請註明
使用方法：sh user_setup.sh myname password
指令碼內容：
usern=$1
passwd=$2
rm /opt/oracle_zip/createUser.sql
touch /opt/oracle_zip/createUser.sql

echo "$a create tablespace tabs datafile '/opt/app/oradata/tabs.dbf' size 5M;" >>/opt/oracle_zip/createUser.sql
echo "$a create TEMPORARY tablespace temptab tempfile '/opt/app/oradata/temptab.dbf' size 5M;" >>/opt/oracle_zip/createUser.sql
echo "$a create user ${usern} identified by ${passwd} default tablespace tabs temporary tablespace temptab;" >>/opt/oracle_zip/createUser.sql
echo "$a grant connect,resource,dba,create any table,select any dictionary to ${usern};" >>/opt/oracle_zip/createUser.sql
echo "$a commit;" >>/opt/oracle_zip/createUser.sql
echo "$a exit;" >>/opt/oracle_zip/createUser.sql

chown -R oracle:dba /opt/oracle_zip/createUser.sql
chmod -R 755 /opt/oracle_zip/createUser.sql

rm /opt/oracle_zip/createTab.sql
touch /opt/oracle_zip/createTab.sql

echo "$a CREATE TABLE DEPT" >>/opt/oracle_zip/createTab.sql
echo "$a (DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY," >>/opt/oracle_zip/createTab.sql
echo "$a DNAME VARCHAR2(14)," >>/opt/oracle_zip/createTab.sql
echo "$a LOC VARCHAR2(13));" >>/opt/oracle_zip/createTab.sql
echo "$a INSERT INTO DEPT VALUES(1,'IT','XIAN');" >>/opt/oracle_zip/createTab.sql
echo "$a commit;" >>/opt/oracle_zip/createTab.sql
echo "$a exit;" >>/opt/oracle_zip/createTab.sql

chown -R oracle:dba /opt/oracle_zip/createTab.sql
chmod -R 755 /opt/oracle_zip/createTab.sql

sqlplus -S "sys/oracle as sysdba" @/opt/oracle_zip/createUser.sql
sleep 5s

chown -R oracle:dba /opt/app/oradata/tabs.dbf
chmod -R 755 /opt/app/oradata/tabs.dbf

chown -R oracle:dba /opt/app/oradata/temptab.dbf
chmod -R 755 /opt/app/oradata/temptab.dbf
 
sqlplus -S "${usern}/${passwd}" @/opt/oracle_zip/createTab.sql