他のユーザーへの成り代わり、制限、リストアについて

SQL> create user a
  2  identified by a;
User created.

SQL> grant
  2  create session,
  3  create procedure
  4  to a;
Grant succeeded.

SQL> create user b
  2  identified by b
  3  default tablespace users
  4  quota unlimited on users;
User created.

SQL> grant
  2  create session,
  3  create table,
  4  create procedure
  5  to b;
Grant succeeded.

SQL> connect b/b
Connected.

SQL> create
  2  procedure p
  3  as
  4  begin
  5      null;
  6  end;
  7  /
Procedure created.

SQL> connect a/a
Connected.

SQL> show user
USER is "A"

SQL> connect a[b]/a
Connected.
SQL> show user
USER is "B"

SQL> create or replace
  2  procedure p
  3  as
  4  begin
  5      dbms_output.put_line
              ( 'new stuff' );
  6  end;
  7  /
Procedure created.

SQL> create table t2 ( x int );
Table created.

SQL> grant execute on p to a;
Grant succeeded.

SQL> connect a/a
Connected.

SQL> exec b.p
new stuff
PL/SQL procedure successfully completed.

SQL> create role b_role1;
Role created.

SQL> create role b_role2;
Role created.

SQL> grant
  2  create procedure,
  3  create session
  4  to b_role1;
Grant succeeded.

SQL> grant
  2  create table
  3  to b_role2;
Grant succeeded.

SQL> create user b
  2  identified by b
  3  default tablespace users
  4  quota unlimited on users;
User created.

SQL> grant b_role1 to b;
Grant succeeded.

SQL> grant b_role2 to b;
Grant succeeded.

SQL> alter user b
  2  grant connect through a
  3  with role b_role1;
User altered.

SQL> connect a[b]/a
Connected.

SQL> show user
USER is "B"

SQL> create or replace
  2  procedure p
  3  as
  4  begin
  5      dbms_output.put_line
              ( 'new stuff' );
  6  end;
  7  /
Procedure created.

SQL> create table t2 ( x int );
create table t2 ( x int )
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant execute on p to a;
Grant succeeded.

SQL> connect b/b
Connected.

SQL> create table t ( x int );
Table created.

 "Jane Doe",2008,03,18,23,59,4 "Tom Thomas",2011,10,31,18,00,59 "Bill Williams",2012,02,31,01,10,5 

 ( patient_name ,v_yyyy boundfiller char ,v_mm boundfiller char ,v_dd boundfiller char ,v_hh boundfiller char ,v_mi boundfiller char ,v_ss boundfiller char  ,service_date "to_date(:v_yyyy  || :v_mm || :v_dd || :v_hh || :v_mi  || :b_ss , 'YYYYMMDDHH24MISS')" ) 

 ORA-29913: error in executing  ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while  parsing access parameters KUP-01005: syntax error: found  "identifier": expecting one of:  "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw,  varcharc, zoned" KUP-01008: the bad identifier was:  boundfiller KUP-01007: at line 13 column 21

LOAD DATA
INFILE '/home/tkyte/t.dat'
INTO TABLE t
REPLACE
FIELDS TERMINATED BY ','
trailing nullcols
(
patient_name 
,v_yyyy boundfiller char 
,v_mm boundfiller char
,v_dd boundfiller char 
,v_hh boundfiller char
,v_mi boundfiller char 
,v_ss boundfiller char
,service_date "to_date(:v_yyyy 
|| :v_mm || :v_dd || :v_hh || :v_mi 
|| :v_ss ,'YYYYMMDDHH24MISS')"
)
 

$ sqlldr / t.ctl external_table=generate_only
 

CREATE TABLE statement
CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
  "PATIENT_NAME" VARCHAR2(30),
  "V_YYYY" VARCHAR2(255), "V_MM" VARCHAR2(255),
  "V_DD" VARCHAR2(255),   "V_HH" VARCHAR2(255),
  "V_MI" VARCHAR2(255),   "V_SS" VARCHAR2(255),
  "SERVICE_DATE" VARCHAR(4000)
)
ORGANIZATION external
( 
  TYPE oracle_loader
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'MY_DIR':'t.bad'
    LOGFILE 't.log_xt' READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS
    ( "PATIENT_NAME" CHAR(255) TERMINATED BY ",",
      "V_YYYY" CHAR(255)       TERMINATED BY ",",
      "V_MM" CHAR(255)         TERMINATED BY ",",
      "V_DD" CHAR(255)         TERMINATED BY ",",
      "V_HH" CHAR(255)         TERMINATED BY ",",
      "V_MI" CHAR(255)         TERMINATED BY ",",
      "V_SS" CHAR(255)         TERMINATED BY ",",
      "SERVICE_DATE" CHAR(255) TERMINATED BY ","
    )
  ) location('t.dat')
)REJECT LIMIT UNLIMITED

INSERT AS SELECT statement
INSERT /*+ append */ INTO T
(
  PATIENT_NAME,
  SERVICE_DATE
)
SELECT
  "PATIENT_NAME",
  to_date("V_YYYY" || "V_MM" || "V_DD" || "V_HH" || "V_MI" || "V_SS" , 
'YYYYMMDDHH24MISS')
FROM "SYS_SQLLDR_X_EXT_T"
 

alter index 
"BIN$/yO2LoFDTmObx1GIQtwxOA==$0" 
rename to PK_SR_TEST1; 
 

SQL> create table t
  2  ( x int,
  3    constraint t_pk primary key(x),
  4    constraint check_x check(x>0)
  5  );
Table created.
 

SQL> column SCN new_val S
SQL> select dbms_flashback.get_system
_change_number SCN
  2    from dual;

       SCN
——————————————
 106788340
 

SQL> drop table t;
Table dropped.
 

SQL> flashback table t
  2  to before drop;
Flashback complete.
 

SQL> column index_name new_val I
SQL> select index_name
  2    from user_indexes
  3   where table_name = 'T';

INDEX_NAME
—————————————————————————————————————————
BIN$zwMim2IndhbgQwEAAH/duw==$0
 

SQL> connect / as sysdba
Connected.

SQL> grant flashback on 
user_indexes to ops$tkyte;
Grant succeeded.

SQL> grant flashback on 
user_constraints to ops$tkyte;
Grant succeeded.
 

SQL> connect /
Connected.

SQL> column index_name new_val OI
SQL> select index_name
  2    from user_indexes as of scn &S

  3   where table_name = 'T';
old   2:   from user_indexes 
as of scn &S

new   2:   from user_indexes 
as of scn  106789036

INDEX_NAME
—————————————————————————————————————————
T_PK
 

SQL> alter index "&I" rename 
to "&OI";
old   1: alter index "&I" rename 
to "&OI"
new   1: alter index 
"BIN$zwNBF4ITdtLgQwEAAH/Atw==$0" 
rename to "T_PK"

Index altered.
 

try
{
  grab a connection
  prepare statement
  execute statement  
   <<<==== this locks some resource
  close statement
  prepare statement2
  execute statement2 
   <<<==== this fails 
  close statement2
  commit
  release connection back to pool
} 
catch (e exception)
{
 print out a silly message somewhere
}