第56/57次：2015-09-05 星期六上下午

botang · 发表于 2015-9-5 21:39:37

本帖最后由 botang 于 2015-9-5 22:02 编辑

select  * from dba_indexes i where i.owner='HR'
  and i.table_name like 'TBIG%';

select  * from dba_constraints c where c.owner='HR'
  and c.table_name like 'TBIG%';


select  segment_name, bytes/1024/1024
from dba_segments s where s.owner='HR' and
s.segment_name  like 'TBIG%';

alter system flush buffer_Cache;

alter system flush shared_pool;

select  * from hr.emp;

select  * from hr.dept;

create table hr.tpasswd
( i_username  varchar2(100),
   i_password  char(1),
   i_uid  number(10),
   i_gid  number(10),
   i_comment  varchar2(100),
   i_home varchar2(60),
   i_shell varchar2(100) ) ;

select  * from hr.tpasswd;
truncate table hr.tpasswd;

select  * from hr.emp;

select  * from hr.proj  order by 1;

truncate table  hr.emp;

select  * from hr.t_bank_invoice;

truncate table hr.t_bank_invoice;

create directory  datadir1  as '/home/oracle/datadir1';

create directory  datadir2  as '/home/oracle/datadir2';

create directory  baddir  as '/home/oracle/baddir';

create directory  logdir  as '/home/oracle/logdir';

grant  read,write  on directory  datadir1 to hr;

grant read,write on directory datadir2  to hr;

grant read, write on directory logdir to hr;

grant read,write on directory baddir to hr;

CREATE TABLE hr.extab_employees
               (employee_id    NUMBER(4),
               first_name       VARCHAR2(20),
                                    last_name       VARCHAR2(25),
                                       hire_date       DATE);

drop table hr.extab_employees;

select  * from dba_tables t where t.owner='HR' ;

select  * from dba_external_tables  t where t.owner='HR';

create directory  dirdump  as '/home/oracle/dirdump';

select  * from dba_datapump_jobs;

select  * from system.FULL_JOB1;

c.ctl：

LOAD DATA
infile 'data02.dat'
APPEND
INTO TABLE HR.EXTAB_EMPLOYEES
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
EMPLOYEE_ID INTEGER EXTERNAL,
FIRST_NAME CHAR,
LAST_NAME CHAR,
HIRE_DATE DATE(11) "DD-mon-YYYY"
)

复制代码

c.sql：

CREATE TABLE hr.extab_employees
(
"EMPLOYEE_ID" NUMBER(4),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25),
"HIRE_DATE" DATE
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DATADIR1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE BADDIR:'data%a_%p.bad'
LOGFILE LOGDIR:'data%a_%p.log'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPLOYEE_ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"FIRST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LAST_NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"HIRE_DATE" CHAR(11)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
DATE_FORMAT DATE MASK "DD-mon-YYYY"
)
)
location
(
DATADIR2:'data02.dat', DATADIR1:'data01.dat'
)
)
parallel
REJECT LIMIT UNLIMITED;

复制代码

[oracle@station90 ~]$ vim 1.txt
[oracle@station90 ~]$ cut -c 1 1.txt
a
[oracle@station90 ~]$ cut -c 1 1.txt > 1a.txt
[oracle@station90 ~]$ cut -c 3 1.txt
b
[oracle@station90 ~]$ cut -c 3 1.txt > 1b.txt
[oracle@station90 ~]$ cut -c 5 1.txt
c
[oracle@station90 ~]$ cut -c 5 1.txt  > 1c.txt
[oracle@station90 ~]$ cut -c 7 1.txt
d
[oracle@station90 ~]$ cut -c 7 1.txt > 1d.txt
[oracle@station90 ~]$ paste -d ',' 1a.txt  1b.txt
a,b
[oracle@station90 ~]$ paste -d ',' 1a.txt  1b.txt > 1ab.txt
[oracle@station90 ~]$ paste -d ','  1ab.txt  1c.txt  > 1abc.txt
[oracle@station90 ~]$ paste -d ','  1abc.txt  1d.txt > 1abcd.txt
[oracle@station90 ~]$ vim 1abcd.txt
[oracle@station90 ~]$ vim 1.txt
[oracle@station90 ~]$ vim 1.txt

my2.par：

directory=dir1
dumpfile=my2.dmp
schemas=hr
job_name=my2_job
exclude=table:"in ('T_BANK_INVOICE')", procedure:"in ('SECURE_DML')"
query='employees:where department_id=90'
flashback_time='2014-10-14:20:34:12'