oracle建立資料表空間和使用者

---oracle建立資料表空間---
CREATE TABLESPACE ssitablespace
 DATAFILE
     '/opt/oracle/db/oradata/iptvrpt/lv_ssitablespace1tbs' SIZE 20M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

---oracle建立使用者---
create user ssi identified by ssi default tablespace ssitablespace temporary tablespace temp;
grant CONNECT to ssi;
grant RESOURCE to ssi;
grant dba to ssi;
alter profile DEFAULT limit failed_login_attempts unlimited password_life_time unlimited

 

----使用ssi使用者登入-----

@database.sql

----完成-----

 

也可以寫出批處理

建立檔案createtablespaceanduser.sh

#!/usr/bin/ksh

 
net_svr="bss"
export ORACLE_SID=$net_svr
link_str="/as sysdba"

echo "create tablespaces start ......"
sqlplus "$link_str" <<EOF1

CREATE TABLESPACE ssitablespace
 DATAFILE
     '/opt/oracle/db/oradata/iptvrpt/lv_ssitablespace1tbs' SIZE 20M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

create user ssi identified by ssi default tablespace ssitablespace temporary tablespace temp;
grant CONNECT to ssi;
grant RESOURCE to ssi;
grant dba to ssi;
alter profile DEFAULT limit failed_login_attempts unlimited password_life_time unlimited

 

exit  

 

link_str="ssi/ssi"

echo  "load data start ....... "

sqlplus "$link_str"

 

@./sql/data.sql

echo  "load data finish ....... "
EOF1
echo "create tablespaces end ......"

----完成-----