oracle tips

一時領域確認

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