第18章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例18.1
conn system/system@oradb
DROP TABLESPACE data_ts1 INCLUDING CONTENTS;
CREATE TABLESPACE data_ts1
    DATAFILE '%ORACLE_HOME%\database\data_ts1.dbf' SIZE 2M REUSE
    UNIFORM SIZE 128K;

例18.2
conn system/system@oradb
DROP TABLESPACE temp_ts1 INCLUDING CONTENTS;
CREATE TEMPORARY TABLESPACE temp_ts1
    TEMPFILE '%ORACLE_HOME%\database\temp_ts1.dbf' SIZE 2M REUSE
    UNIFORM SIZE 128K;

例18.3
conn system/system@oradb
ALTER USER scott TEMPORARY TABLESPACE temp_ts1;
CREATE USER stu05 IDENTIFIED BY stu05
  DEFAULT TABLESPACE data_ts1 TEMPORARY TABLESPACE temp_ts1;

例18.4
conn system/system@oradb
DROP TABLESPACE undo_ts1 INCLUDING CONTENTS;
CREATE UNDO TABLESPACE undo_ts1
   DATAFILE '%ORACLE_HOME%\database\undo_ts1.dbf' SIZE 2M REUSE;

例18.5 
conn system/system@oradb
DROP TABLESPACE temp_ts1 
 INCLUDING CONTENTS AND DATAFILES
 CASCADE CONSTRAINTS;

例18.6 
conn system/system@oradb
DROP ROLLBACK SEGMENT undo_ts1_rbs1;
CREATE ROLLBACK SEGMENT undo_ts1_rbs1
  TABLESPACE undo_ts1 STORAGE (initial 5K
                                 next 5K
                                 optimal 500K
                                 minextents 15
                                 maxextents 100) ;

例18.7
ALTER TABLESPACE my_index 
  ADD DATAFILE '%ORACLE_HOME%\database\my_index_data1.dbf' SIZE 1M;
ALTER DATABASE 
  DATAFILE '%ORACLE_HOME%\database\my_index_data1.dbf' RESIZE 2M ;
ALTER DATABASE DATAFILE '%ORACLE_HOME%\database\my_index_data1.dbf'
  AUTOEXTEND ON NEXT 1M MAXSIZE 10M;

例18.9 
BUFFER=999999
FILE=d:\data\scott.dmp
COMPRESS=Y
CONSTRAINTS=Y
GRANTS=Y
INDEXES=Y
TABLES=(dept,emp)
ROWS=Y
EXP scott/tiger@oradb parfile=d:\data\exp_scott.txt

例18.10 
BUFFER=999999
FILE=d:\data\all20050530.dmp
COMPRESS=Y
CONSTRAINTS=Y
GRANTS=Y
INDEXES=N
ROWS=N
FULL=Y
EXP system/system@oradb parfile=d:\data\exp_all.txt

例18.12 
BUFFER=999999
FILE=d:\data\scott.dmp
GRANTS=Y
IGNORE=Y
FROMUSER=scott
TOUSER=scott
IMP scott/tiger@oradb parfile=d:\data\imp_scott.txt

例18.13
conn scott/tiger@oradb
DROP TABLE example;
/
CREATE TABLE example(
col1 number(3),
col2 varchar2(10));
--example.dat
001,cd,
0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
--exam_loader.ctl
load data
infile 'd:\data\example.dat'
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
SQLLDR scott/tiger@oradb control=d:\data\exam_loader.ctl

例18.14
emp_loader.dat
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80        960                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81       1920        800         30
      7521 WARD       SALESMAN        7698 22-2月 -81       1500        800         30
      7566 JONES      MANAGER         7839 02-4月 -81       3570                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81       1500        800         30
      7698 BLAKE      MANAGER         7839 01-5月 -81       3420        800         30
      7782 CLARK      MANAGER         7839 09-6月 -81       2940                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87       3600                    20
      7839 KING       PRESIDENT            17-11月-81       6000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81       1800        800         30
      7876 ADAMS      CLERK           7788 23-5月 -87       1320                    20
      7900 JAMES      CLERK           7698 03-12月-81       1140        800         30
      7902 FORD       ANALYST         7566 03-12月-81       3600                    20
      7934 MILLER     CLERK           7782 23-1月 -82       1560                    10
      1010 John                                             2400        400         10
conn scott/tiger@oradb
set linesize 86
set pagesize 100
spool d:\data\emp_loader.dat
select * from emp_2;
spool off
--d:\data\emp_loader.ctl
    OPTIONS (SKIP=2, LOAD=5 )
    LOAD DATA
    INFILE 'd:\data\emp_loader.dat'
    BADFILE 'd:\data\emp_loader.bad'
    DISCARDFILE 'd:\data\emp_loader.dsc'
    APPEND
    INTO TABLE emp
    (
       empno  POSITION(1:10),
       ename  POSITION(12:21),
       job    POSITION(23:31),
       mgr    POSITION(33:42),
       hiredate POSITION(44:53),
       sal    POSITION(55:64),
       comm   POSITION(66:75),
       deptno POSITION(77:86)
    )
SQLLDR scott/tiger@oradb control=d:\data\emp_loader.ctl
SQLLDR scott/tiger@oradb control=d:\data\emp_loader.ctl skip=2 load=5