資料庫建使用者指令碼install.sh

標籤：tab   恢複   根據   immediate   exec   lin   tables   資料   手工   

--建立oracle使用者和資料表空間, 僅首次安裝系統時需要執行.
--若之前已建立同名的資料表空間或使用者, 則對應指令碼會被忽略.
declare
---------------------------------------------------------------
-- 以下配置請根據實際部署情況手工修改
---------------------------------------------------------------
--使用者名稱
v_username varchar2(1000) := ‘test‘;
--密碼
v_password varchar2(1000) := ‘1‘;
--資料檔案目錄, 這個目錄在ORACLE伺服器, 需要和DBA確認.
v_datafile_path varchar2(1000) := ‘/home/oracle/app/oradata/orcl‘;

--存放資料泵備份檔案的目錄, 這個目錄在ORACLE伺服器, 需要和DBA確認. 對應linux系統的目錄需要手工建立?
--v_directory_path varchar2(1000) := ‘/home/oracle/product/admin/ora11g/dpdump‘;
---------------------------------------------------------------
-- 以下資訊請勿修改
---------------------------------------------------------------
v_count number;
v_dir_name varchar2(1000); --目錄名稱
v_ts_name varchar2(1000); --當前庫資料表空間
begin
v_username := LOWER(v_username);
v_dir_name := LOWER(‘fmdmp_‘ || v_username);
v_ts_name := LOWER(v_username);

????--建立資料備份與恢複目錄
????select count(1) into v_count from dba_directories a where a.directory_name = UPPER(v_dir_name);
????if v_count = 0 then
???? execute immediate ‘create directory ‘ || v_dir_name || ‘ as ‘‘‘ || v_directory_path || ‘/‘ || v_username || ‘‘‘‘;
????end if;

????--建立當前庫資料表空間
????select count(*) into v_count from dba_tablespaces a where a.tablespace_name = UPPER(v_ts_name);
????if (v_count = 0) then
????????execute immediate ‘create tablespace ‘
|| v_ts_name || ‘ logging datafile ‘‘‘ || v_datafile_path || ‘/‘ || v_ts_name || ‘.dbf‘‘ size 512M‘
|| ‘ autoextend on next 512M‘
|| ‘ extent management local‘
|| ‘ segment space management auto‘;
????end if;
????
????--建立當前庫使用者
????select count(*) into v_count from dba_users a where a.username = UPPER(v_username);
????if (v_count = 0) then
????????execute immediate ‘create user ‘ || v_username || ‘ identified by ‘ || v_password || ‘ default tablespace ‘ || v_ts_name || ‘ temporary tablespace TEMP‘;
????end if;
execute immediate ‘grant connect, resource, dba, unlimited tablespace, create any table, select any table, grant any object privilege, create any synonym, drop any synonym to ‘ || v_username;
execute immediate ‘grant execute on utl_file to ‘ || v_username;
--授權目錄給當前庫使用者
????execute immediate ‘grant read,write on directory ‘ || v_dir_name || ‘ to ‘ || v_username;
????
????--授權查詢dba_directories給目前使用者
????execute immediate ‘grant select on sys.DBA_DIRECTORIES to ‘ || v_username;
end;
/

資料庫建使用者指令碼install.sh