#一時領域確認
SQL# col tablespace_name format a20;
SQL# col file_name format a40;
SQL# select tablespace_name, file_name from dba_temp_files;
TEMP /u01/app/oracle/oradata/orcl/temp01.dbf
SQL# alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 30G; <--拡大
SQL# alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on; <--自動拡張
#シェル一気にユーザー(user1)作成
#!/bin/bash
sqlplus /nolog <<EOS
conn / as sysdba
drop user user1 cascade;
create user user1 identified by user1 default tablespace users temporary tablespace temp;
grant connect to user1;
grant resource to user1;
grant CREATE any VIEW to user1;
grant drop any view to user1;
grant update any table to user1;
grant delete any table to user1;
grant select any table to user1;
grant insert any table to user1;
grant unlimited tablespace to user1;
grant create session to user1;
EOS
exit 0
#expdpとimpdp
ディレクトリ作って
$ mkdir -p /home/oracle/dump_dir;
マッピングして
SQL# drop directory dump_dir;
SQL# create directory dump_dir as '/home/oracle/dump_dir';
SQL# grant read, write on directory dump_dir to user1;
実行
$ expdp user1/user1@db01 directory=dump_dir dumpfile=user1.dmp
$ impdp system/system directory=dump_dir dumpfile=user1.dmp remap_schema=user1:user2
#sqlplusでprocedure実行
create or replace procedure select_t_zeimu (bb out number) as
varID number := 1;
begin
select count(*) into bb from t_zeimu;
dbms_output.put_line('count:' || bb);
end;
/
set serveroutput on
variable bb number;
execute select_t_zeimu(:bb: );
#いろいろ確認
SQL> select * from user_role_privs; <--ロール確認
SQL> select * from user_sys_privs; <--権限確認
SQL> select * from all_users order by created ; <--ユーザー一覧
sqlplusで日本語が化ける時
export NLS_LANG=Japanese_Japan.AL32UTF8