-- Oracle外部表
--=================
外部表只能在Oracle 9i之后来使用。简单地说，外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，我们
可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。
一、外部表的特性
位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。
ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。
二、创建外部表的注意事项
1.需要先建立目录对象
2.对于操作系统文件的要求
文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件
3.在建立临时表时的相关限制
对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。
简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。而没有存储实际的数据。
由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。
4.删除外部表或者目录对象
一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后
再删除目录对象。
如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到"对象不存在"的错误信息。
查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。
5.对于操作系统平台的限制
不同的操作系统对于外部表有不同的解释和显示方式
如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响
三、创建外部表
使用CREATE TABLE语句的ORGANIZATION EXTENERAL子句来创建外部表。外部表不分配任何盘区，因为仅仅是在数据字典中创建元数据。
1.外部表的创建语法
createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
organization exteneral
(.....)
2.由查询结果集，使用Oracle_datapump来填充数据来生成外部表
a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限
[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data
sys@ORCL>createorreplacedirectory dat_diras'/home/oracle/external_tb/data/';
sys@ORCL>grantread,writeondirectory dat_dirtoscott;
b.创建外部表
scott@ORCL>createtableex_tb1 --创建外部表
2 (ename,job,sal,dname) --表列描述，注意未指定数据类型
3 organizationexternal
4 (
5 typeoracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select 生成，故不支持oracle_loader
6 defaultdirectory dat_dir --指定外部表的存放目录
7 location('tb1.exp','tb2.exp') --产生外部表的内容将填充到这些文件中
8 )
9 parallel --按并行方式来填充
10 as
11 selectename,job,sal,dname -填充使用的原始数据
12 fromempjoindept
13 onemp.deptno=dept.deptno;
c.--验证外部表
scott@ORCL>select*fromex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
WARD SALESMAN 1250 SALES
JONES MANAGER 2975 RESEARCH
..........
对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。
3.使用SQLLDR提供外部表的定义并创建外部表
关于SQL*Loader的使用请参照：SQL*Loader使用方法
我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义
[oracle@oradb ~]$ cat demo1.ctl
LOADDATA
INFILE*
INTOTABLEDEPT_NEW
FIELDS TERMINATEDBY','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE参数有以下三个值：
NOT_USED：默认值。
EXECUTE：这个值说明SQLLDR不会生成并执行一个SQLINSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。
GENERATE_ONLY：使SQLLDR并不具体加载任何数据，而只是会生成所执行的SQL DDL和DML 语句，并放到它创建的日志文件中。
注：DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。
[oracle@oradb ~]$ cat demo1.log --查看sqlldr产生的日志文件
TableDEPT_NEW,loadedfromevery logical record.
Insertoptionineffectforthistable:INSERT
ColumnName Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
CREATEDIRECTORY statements neededforfiles --创建一个目录
------------------------------------------------------------------------
CREATEDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000AS'/home/oracle/'
CREATETABLEstatementforexternaltable: --生成创建外部表的命令
------------------------------------------------------------------------
CREATETABLE"SYS_SQLLDR_X_EXT_DEPT_NEW"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(20),
"LOC" VARCHAR2(20)
)
ORGANIZATIONexternal --该子句表明是一个外部表heap对应普通表，index对应iot，external对应外部表
(
TYPEoracle_loader --说明外部文件访问方式：oracle_loader或
oracle_datapump(9i不支持)
DEFAULTDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目录
ACCESS PARAMETERS --这个访问参数有些类似于sqlldr中控制
文件中的描述信息
( --系统根据这些描述信息来生成外部表的格式RECORDS DELIMITEDBYNEWLINE CHARACTERSET US7ASCII --记录默认以换行符结束
BADFILE'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放处理失败的记录文件描述
LOGFILE'demo1.log_xt' --日志文件
READSIZE 1048576--Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式则从SGA分配
SKIP 6 --跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过
FIELDS TERMINATEDBY"," LDRTRIM --描述字段的终止符
REJECT ROWSWITHALLNULLFIELDS --所有为空值的行被跳过并且记录到bad file.
( --下面是描述外部文件各个列的定义
"DEPTNO"CHAR(255)
TERMINATEDBY",",
"DNAME"CHAR(255)
TERMINATEDBY",",
"LOC"CHAR(255)
TERMINATEDBY","
)
)
location
(
'demo1.ctl' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED --描述允许的错误数，此处为无限制
INSERTstatements usedtoloadinternal tables: --用于将数据填充到表，使用append 方式
------------------------------------------------------------------------
INSERT/*+ append */INTODEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM"SYS_SQLLDR_X_EXT_DEPT"
statementstocleanup objects createdbyprevious statements: --用于删除目录和外部表的定义信息
------------------------------------------------------------------------
DROPTABLE"SYS_SQLLDR_X_EXT_DEPT_NEW"
DROPDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
----------------------------------------------------------------------------------------------------------------------- sys@ORCL>grantcreateanydirectorytoscott;
sys@ORCL>grantdropanydirectorytoscott;
scott@ORCL>createtabledept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
scott@ORCL>select*fromdept_new;
norows selected
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute
scott@ORCL>select*fromdept_new;
DEPTNO DNAME LOC
---------- -------------------- -------------------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
4.使用平面文件定义并生成外部表
a.平面文件数据
1.dat：
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
2.dat：
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
b.继续使用前面创建的目录/home/oracle/external_tb/data来存放数据文件：
sys@ORCL>select*fromdba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- --------------- ---------------------------------------------
SYS DATA_PUMP_DIR /u01/oracle/10g/rdbms/log/
SYS DAT_DIR /home/oracle/external_tb/data/
scott@ORCL>ho ls/home/oracle/external_tb/data/
1.dat
2.dat tb1.exp tb2.exp
c.创建外部表
scott@ORCL>get/u01/bk/scripts/tb.emp_new
1 createtableemp_new
2 (
3 emp_id number(4),
4 ename varchar2(15),
5 job varchar2(12),
6 mgr_id number(4),
7 hiredate date,
8 salary number(8),
9 comm number(8),
10 dept_id number(2)
11 )
12 organizationexternal
13 (
14 typeoracle_loader
15 defaultdirectory dat_dir
16 access parameters
17 (
18 records delimitedbynewline
19 fields terminatedby','
20 )
21 location
22 ('1.dat','2.dat')
23*);
scott@ORCL>start/u01/bk/scripts/tb.emp_new
d.验证外部表
scott@ORCL>select*fromemp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 100 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
............................
scott@ORCL>deletefromemp_newwhereename='SMITH'; --外部表不能执行DML
deletefromemp_newwhereename='SMITH'
*
ERROR at line 1:
ORA-30657:operationnotsupportedonexternalorganizedtable
scott@ORCL>insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual;
insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual
*
ERROR at line 1:
ORA-30657:operationnotsupportedonexternalorganizedtable
e.获得外部表的有关信息：
scott@ORCL>col access_parameters format a35
scott@ORCL>selectowner,table_name,type_name,default_directory_name,access_parameters
2 fromdba_external_tables;
OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS
---------- --------------- ------------------------------ --------------- -----------------------------------
SCOTT EX_TB1 ORACLE_DATAPUMP DAT_DIR
SCOTT EMP_NEW ORACLE_LOADER DAT_DIR
records delimitedbynewline
fields terminatedby','
SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimitedbynewline
fields terminatedby','
f.获得平面文件的位置，使用如下的查询：
scott@ORCL>select*fromdba_external_locationsorderbytable_name;
OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
---------- --------------- --------------- --- ------------------------------
SCOTT EMP_NEW 1.dat SYS DAT_DIR
SCOTT EMP_NEW 2.dat SYS DAT_DIR
SCOTT EMP_PUMP 1.dat SYS DAT_DIR
SCOTT EMP_PUMP 2.dat SYS DAT_DIR
SCOTT EX_TB1 tb2.exp SYS DAT_DIR
SCOTT EX_TB1 tb1.exp SYS DAT_DIR
5.外部表定义的进一步分析
CREATETABLEexternal_table
(
COL01 VARCHAR2(100),
COL02 NUMBER,
......
)
ORGANIZATIONEXTERNAL
(
TYPEORACLE_LOADER
DEFAULTDIRECTORY "XXX"
ACCESS PARAMETERS
(
RECORDS DELIMITEDBY0X'0A'
SKIP 1
BADFILE'bad.txt'
FIELDS TERMINATEDBY','
OPTIONALLY ENCLOSEDBY'"'
LRTRIM MISSING FIELDVALUESARENULL
REJECT ROWSWITHALLNULLFIELDS
)
LOCATION
("CJ_DIR":'data.txt')
)REJECT LIMIT UNLIMITED;
外部表定义的几个重点
https://www.doczj.com/doc/4e15311307.html,ANIZATIONEXTERNAL关键字，必须要有。以表明定义的表为外部表。
b.重要参数外部表的类型
ORACLE_LOADER：定义外部表的缺省方式，只能只读方式实现文本数据的装载。
ORACLE_DATAPUMP：支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也
可以从内部表卸载数据作为二进制文件填充到外部表。
c.DEFAULTDIRECTORY：缺省的目录指明了外部文件所在的路径
d.LOCATION：定义了外部表的位置
f.ACCESS PARAMETERS：描述如何对外部表进行访问
RECORDS关键字后定义如何识别数据行
DELIMITEDBY'XXX'——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，
如特殊符号，可以使用OX'十六位值'，例如tab(/t)的十六位是9，则DELIMITEDBY0X'09'；
cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。
SKIP X ——跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。
FIELDS关键字后定义如何识别字段，常用的如下：
FIELDS：TERMINATEDBY'x'——字段分割符。
ENCLOSEDBY'x'——字段引用符，包含在此符号内的数据都当成一个字段。
例如一行数据格式如："abc","a""b,""c,"。使用参数TERMINATEDBY','ENCLOSEDBY'"'后，系统会读到两个字段，
第一个字段的值是abc，第二个字段值是a"b,"c,。
LRTRIM ——删除首尾空白字符。
MISSING FIELDVALUESARENULL——某些字段空缺值都设为NULL。
对于字段长度和分割符不确定且准备用作外部表文件，可以使用UltraEdit、Editplus 等来进行分析测试，如果文件较
大，则需要考虑将文件分割成小文件并从中提取数据进行测试。
外部表对错误的处理
REJECT LIMIT UNLIMITED
在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制
BADFILE和NOBADFILE子句
用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误
如果未指定该参数，则系统自动在源目录下生成与外部表同名的.BAD文件
BADFILE记录本次操作的结果，下次将会被覆盖
LOGFILE和NOLOGFILE子句
同样在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中
而NOLOGFILE子句则表示不记录错误信息到log中，如忽略该子句，系统自动在源目录下生成与外部表同名的.LOG文件
注意以下几个常见的问题
1.外部表经常遇到BUFFER不足的情况，因此尽可能的增大READSIZE
2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样，碰到错误日志提示如是换行符问题，可以使用
UltraEdit打开，直接看十六进制
3.特定行报错时，查看带有"BAD"的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突
1.视图的概述 视图其实就是一条查询sql语句，用于显示一个或多个表或其他视图中的相关数据。视图将一个查询的结果作为一个表来使用，因此视图可以被看作是存储的查询或一个虚拟表。视图来源于表，所有对视图数据的修改最终都会被反映到视图的基表中，这些修改必须服从基表的完整性约束，并同样会触发定义在基表上的触发器。(Oracle支持在视图上显式的定义触发器和定义一些逻辑约束) 2.视图的存储 与表不同，视图不会要求分配存储空间，视图中也不会包含实际的数据。视图只是定义了一个查询，视图中的数据是从基表中获取，这些数据在视图被引用时动态的生成。由于视图基于数据库中的其他对象，因此一个视图只需要占用数据字典中保存其定义的空间，而无需额外的存储空间。 3.视图的作用 用户可以通过视图以不同形式来显示基表中的数据，视图的强大之处在于它能够根据不同用户的需要来对基表中的数据进行整理。视图常见的用途如下： 通过视图可以设定允许用户访问的列和数据行，从而为表提供了额外的安全控制 隐藏数据复杂性 视图中可以使用连接(join)，用多个表中相关的列构成一个新的数据集。此视图就对用户隐藏了数据来源于多个表的事实。 简化用户的SQL 语句 用户使用视图就可从多个表中查询信息，而无需了解这些表是如何连接的。 以不同的角度来显示基表中的数据 视图的列名可以被任意改变，而不会影响此视图的基表 使应用程序不会受基表定义改变的影响 在一个视图的定义中查询了一个包含4 个数据列的基表中的3 列。当基表中添加了新的列后，由于视图的定义并没有被影响，因此使用此视图的应用程序也不会被影响。 保存复杂查询 一个查询可能会对表数据进行复杂的计算。用户将这个查询保存为视图之后，每次进行类似计算只需查询此视图即可。
Oracle初级试题 一、选择题（20% 不定项每题2分） 1.对于Oracle用户方案下，根据模式/映像，请选出不属于模式的对象（D） A．表B．索引C．视图D．约束 2.在客户端配置哪个文件，可以使客户端访问oracle server？（A） A．tnsnames.ora B．sqlnet.ora C．listener.ora D．init.ora 3.搜索列中的数据，但只记得部分字符串，通常可以用以下哪种运算？（C） A．in B．between C．like D．exists 4.如从表中删除了一条记录，以下哪个语句可以保存数据库中的改变？（B） A．savepoint B．commit C．rollback D．update 5.下面关于视图的说法，请选出正确的（ABCD） A．一个视图可以创建为只读 B．一个视图可以用来连接两张或更多的表而被创建 C．在视图中不能含有order by子句 D．在视图中不能含有group by子句 6.在select语句中包含了where子句，请问group by子句可以放在哪里？（D） A．where子句前面 B．from子句前面 C．order by子句后面 D．where子句后面 7.关于having子句，以下说法正确的是：（C） A．having与where相同 B．having用于单行条件查询 C．having用于已分组的条件查询 D．having子句必须紧跟在where子句后面使用 8.能够在外部表上进行的操作有：（A） A．select B．update C．insert D．delete 9.在更改索引的操作（alter index）中，以下哪些子句可以定期优化索引？（AC） A．rename B．coalesce C．rebuild D．deallocate unused 10.请选出能够产生锁的操作：（AB） A．select … for update B．commit C．rollback D．lock table 二、填充题（20% 每空4分） 1.数据库最常用的DML操作有SELECT、UPDATE 、DELETE 和INSERT 等（要求写出操作命令）。 2.假设有两个数据N1和N2，请用函数实现如下算法：当N1>N2时，返回“大于”； 当N1=N2时，返回“等于”；当N1
ORACLE AWR报告生成和分析 Automatic Workload Repository是10g引入的一个重要组件。在里面存贮着近期一段时间内，默认是7天，数据库活动状态的详细信息。 AWR报告是对AWR视图进行查询而得到的一份自动生成的报告。可以通过下面的脚本手工得到一份AWR报告。 exec dbms_workload_repository.create_snapshot; ... running the specified workload exec dbms_workload_repository.create_snapshot; @?/rdbms/admin/awrrpt 通过AWR和AWR报告，DBA可以容易地获知最近数据库的活动状态，数据库的各种性能指标的变化趋势曲线，最近数据库可能存在的异常，分析数据库可能存在的性能瓶颈从而对数据库进行优化。 AWR报告所有的数据来源于AWR视图，即以DBA_HIST_开头的所有系统表，Database Reference有对所有这些系统表的描述，这应该是Oracle官方对AWR报告的官方注释了。 而对于如何有效地去分析AWR报告，这可能更需要DBA经验的日积月累。 AWR的前身是Statspack，Statspack在10g和11g中也有提供，同时和AWR 一起做了同步更新，而且Statspack是公开源代码的，因此，关于Statspack的资料，还有Statspack的源代码，都是理解AWR的一个有用的辅助。 如果关注数据库的性能，那么当拿到一份AWR报告的时候，最想知道的第一件事情可能就是系统资源的利用情况了，而首当其冲的，就是CPU。 而细分起来，CPU可能指的是 ●OS级的User%, Sys%, Idle% ●DB所占OS CPU资源的Busy% ●DB CPU又可以分为前台所消耗的CPU和后台所消耗的CPU
配置sqlnet.ora限制IP访问Oracle 与防火墙类似的功能，Oracle 提供限制与允许特定的IP或主机名通过Oracle Net来访问数据库。这个功能由sqlnet.ora配置文件来实 现。该文件通常$ORACLE_HOME/network/admin/ 目录下，与tnsnames.ora以及listener.ora位于同一路径。用法也比较简单。通过监听器的 限制，实现轻量级访问限制，比在数据库内部通过触发器进行限制效率要高。 1. 实现 通过在sqlnet.ora文件中增加下列记录来实现 tcp.validnode_checking = yes tcp.invited_nodes = (hostname1, hostname2，ip1,ip2) tcp.excluded_nodes = (10.103.11.17,hostname1,hostname2) 当使用invited_nodes时，则所有没有包含在invited_nodes值中的IP或主机将无法通过Oracel Net连接到数据库。而如果使用 excluded_nodes时，除了excluded_nodes值中列出的IP和主机不可访问之外，其余的节点都可以访问数据库。通常情况下，更倾向于使 用excluded_nodes参数。 2. 注意 使用excluded_nodes与invited_nodes的一些特性 不支持通配符的使用(如hostname不能写为svhs0*，IP地址不能写为 10.103.11.*） excluded_nodes与invited_nodes为互斥方式，要么使用前者，要么使用后者 如果tcp.invited_nodes与tcp.excluded_nodes都存在，则tcp.invited_nodes 优先 要将本地地址，或者Cluster群集其他节点的地址都加入到允许列表，否则监听器可能无法启动 修改之后，一定要重起监听或reload才能生效，而不需要重新启动数据库仅提供对TCP/IP协议的支持 3. 实战 -->使用tnsping demo92，连接正常 C:\>tnsping demo92 TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 18:55:39
PO模块常用表结构 表名: PO.PO_REQUISITION_HEADERS_ALL 说明: 采购请求 REQUISITION_HEADER_ID NUMBER PR头标识码 SEGMENT1VARCHAR2(20)PR号 ENABLE_FLAG VARCHAR2(1)使能标志(Y/N) AUTHORIZATION_STATUS VARCHAR2(25)批准标志(APPROVED/) TYPE_LOOKUP_CODE VARCHAR2(25)类型(PURCHASE) REQUIST_ID NUMBER请求标识码 CANCEL_FLAG VARCHAR2(1)取消标志(Y/N) TRANSFERRED_TO_OE_FLAG VARCHAR2(1)可否转入OE标志(Y/N) PREPARER_ID NUMBER准备人ID(可与HR.PER_PEOPLE_F.PERSON_ID关联） 表名: PO.PO_REQUISITION_LINES_ALL 说明: 采购请求明细 REQUISITION_LINE_ID NUMBER PR行ID REQUISITION_HEADER_ID NUMBER PR头ID LINE_NUM NUMBER栏目 LINE_TYPE_ID NUMBER行类别 CATEGORY_ID NUMBER归类标识码 ITEM_DESCRIPTION VARCHAR2(240)项目描述 UNIT_MEAS_LOOKUP_CODE VARCHAR2(25)单位 UNIT_PRICE NUMBER单价(已折为人民币) QUANTITY NUMBER数量 DELIVER_TO_LOCATION_ID NUMBER交货位置码（与HR.HR_LOCATIONS.LOCATION_ID关联）TO_PERSON_ID NUMBER收货人代码 SOURCE_TYPE_CODE VARCHAR2(25)来源类型 ITEM_ID NUMBER项目内码 ITEM_REVISION VARCHAR2(3)项目版本 QUANTITY_DELIVERED NUMBER已交付数量 SUGGESTED_BUYER_ID NUMBER建议采购员代码 ENCUMBERED_FLAG VARCHAR2(1)分摊标志 RFQ_REQUIRED_FLAG VARCHAR2(1) NEED_BY_DATE DATE需求日期(原始) LINE_LOCATION_ID NUMBER定位行标识码(为空时表示未生成PO) MODIFIED_BY_AGENT_FLAG VARCHAR2(1)被采购员更改标志（被拆分Y/NULL）
外部表介绍 ORACLE外部表用来存取数据库以外的文本文件（Text File）或ORACLE专属格式文件。因此，建立外部表时不会产生段、区、数据块等存储结构，只有与表相关的定义放在数据字典中。外部表，顾名思义，存储在数据库外面的表。当存取时才能从ORACLE专属格式文件中取得数据，外部表仅供查询，不能对外部表的内容进行修改（INSERT、UPDATE、DELETE操作）。不能对外部表建立索引。因为创建索引就意味着要存在对应的索引记录。而外部表其实在没有存储在数据库中。故在外部是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不受支持”的错误提示。 Notice: 外部表是ORACLE 9i后引入的。 外部表特征 （1）位于文件系统之中（一定要在数据库服务器中，而不是其它网络路径），按一定格式分割， 例如@#$等，文本文件或者其他类型的文件可以作为外部表。 （2）对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。 （3）外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。 （4） ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。 （5）可以查询操作和连接。也可以并行操作。 （6）数据在数据库的外部组织，是操作系统文件。 （7）操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。 外部表范例： 1：创建目录对象并授权
从9i开始，ORACLE数据库若需要存取文件系统，就必须使用目录对象，以相对路径方式存取文件，强化数据库的安全性。建立目录对象、授予权限。 SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab'; 给用户授予指定目录的操作权限 SQL>GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL; 2：创建外部表 我创建了一个平面文件作为测试用例，如下所示，总共5条记录，其中一条错误记录[oracle@DB-Server exterltab]$ more student.data 10001@#$kerry@#$male@#$28@#$1 10002@#$jimmy@#$male@#$22@#$1 10003@#$ken@#$male@#$21@#$1 10004@#$merry@#$femal@#$20@#$1 this is a bad file CREATE TABLE EXTER_TEST ( ID NUMBER(5) , NAME VARCHAR(12) , SEX VARCHAR(8) , AGE NUMBER(3) , GRADE NUMBER(1) ) ORGANIZATION EXTERNAL ( type oracle_loader default directory dump_dir access parameters ( records delimited by newline fields terminated by'@#$' ) location ('student.data') );
Oracle常用数据字典表（系统表或系统视图）及查询SQL 2014年12月15日?数据库?共4187字?暂无评论?阅读861 次 文章目录 ?数据字典分类 ?dba_开头 ?user_开头 ?v$开头 ?all_开头 ?session_开头 ?index_开头 ?伪表 ?数据字典常用SQL查询 数据字典是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息的视图等。 数据字典系统表，保存在system表空间中。查询所有数据字典可用语句“select * from dictionary;”。 数据字典分类 数据字典主要可分为四部分： 1）内部RDBMS表：x$*，用于跟踪内部数据库信息，维持DB的正常运行。是加密命名的，不允许sysdba以外的用户直接访问，显示授权不被允许。
2）数据字典表：*$，如tab$,obj$,ts$等，用来存储表、索引、约束以及其他数据库结构的信息。 3）动态性能视图：gv$*，v$*，记录了DB运行时信息和统计数据，大部分动态性能视图被实时更新以反映DB当前状态。 4）数据字典视图：user_*、all_*、dba_*，在非Sys用户下，我们访问的都是同义词，而不是V$视图或GV视图。 数据库启动时，动态创建x$，在X$基础上创建GV$，在GV$基础上创建V$X$表-->GV$(视图)--->V$(视图)。 数据字典视图可分为静态数据字典视图和动态数据字典视图。 静态数据字典是指在用户访问数据字典时内容不会发生改变。这类数据字典主要是由表和视图组成，应该注意的是，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。 静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*（该用户方案对象的信息）、all_*（该用户可以访问的所有对象的信息）、dba_*（全部数据库对象的信息）。 动态数据字典是Oracle包含的一些潜在的由系统管理员如SYS维护的表和视图，由于当数据库运行的时候它们会不断进行更新，所以称它们为动态数据字典。这些视图提供了关于内存和磁盘的运行情况，所以我们只能对其进行只读访问而不能修改它们。Oracle中这些动态性能视图都是以v$开头的视图，比如v$access。 dba_开头 dba_users数据库用户信息
课程内容： 一、WEB网页开发 1、HTML语言介绍和标记 2、HTML标记和CSS样式 3、CSS样式属性介绍 4、JAVA Script基础 5、JAVA Script对象 二、Java面向对象编程 1、Java工作原理 2、Java开发环境的搭建 3、标识符、关键字和数据类型 4、运算符号与表示式 5、流程控制 6、数组 7、面对对象的概念 8、类和对象 9、封装和访问控制 10、继承 11、多态 12、抽象类 13、接口 14、嵌套类 15、匿名内部类 16、面向对象与例子 17、异常处理 18、文件系统 19、字节流字符流的转换 20、字节流 21、字符集编码 22、对象流、压缩流、和随机访问流 23、枚举类型 24、集合类 25、多线程 26、AWT和SWING 27、鼠标事件和键盘事件 28、高级组件 29、Eclipse使用 30、Applet 31、网络编程 32、Java安全与反射 33、Java工具类增补 34、MYSQL的应用 35、SQL语句 36、PD数据库设计工具 37、JDBC 三、Java Web开发技术 1、WTP的安装与使用 2、Servlet与Servlet容器 3、Tomcat的安装与使用 4、Servlet容器Servlet API 5、HTTP通信协议 6、web浏览器与HIIP方法 7、Httpservlet与请求处理 8、Http请求分析与响应分析 9、Servlet生命周期 10、ServletConfig分析 11、ServletContext分析 12、请求传送（ServletRequest） 13、web应用与部署 14、监听器 15、过滤器基础 16、过滤器配置 17、过滤器应用与实例 18、使用URL重写管理会话 19、WEB应用安全 20、BASIC验证机制 21、DIGEST验证机制 22、FORM验证机制 23、SERVLETCONTEXT分析 24、SERVLET异常处理机制 25、SERVLET综合应用实例 26、JSP初探 27、JSP标准指令 28、JSP程序代码元素 29、JSP标准动作元素 30、JSP隐含变量 31、JSP与JAVABEAN 32、JSP异常处理机制 33、JSP静态、动态复用 34、自定义标签及其生命周期 35、标签库综合应用实例 36、JSP+SERVLET+JSTL+EL综合应用实例 37、设计模式初探 38、SCWCD涉及的设计模式 四、SSH集成开发技术 1、Oracle的基础 2、PLSQL 3、多表级联 4、JDBC高级数据库编程 5、XML基础 6、XML校验 7、XML解析 8、Struts入门介绍 9、Struts配置 10、Struts国际化 11、Struts验证框架 12、Struts异常处理 13、Struts标签库 14、Struts Tiles框架 15、综合应用实例 16、Web Work介绍 17、Struts2.0介绍 18、Hibernate入门介绍 19、Hibernate配置 20、Hibernate关联关系映射 21、Hibernate事务 22、Hibernate缓存 23、Hibernate高级配置 24、Spring入门介绍 25、Spring Bean和Beanfactory 26、Spring IOC 27、Spring AOP 28、Spring整合Struts 29、Spring+Struts应用实例 30、Spring整合Hibernate 31、声明式事务处理 32、Spring+Hibernate应用实例 33、Spring+Hibernate+Hibernate集成配 置 五、Oracle数据库管理技术 1、Oracle Database 10g：SQL 1）SQL语句的方式 2）基本SQL Select语句 3）过滤和排序 4）单行函数 5）多表查询 6）组函数 7）子查询 8）利用变量交互查询 9）创建和管理表 10）约束介绍 11）创建和管理视图 12）创建和管理序列 13）创建和管理索引 14）创建和管理同义词 15）控制用户访问 16）SET操作符 17）GROUP BY子句的增强 18）高级子查询 19）层次查询 20）Oracle外部表 2、Oracle Database 10g：PL/SQL 1）PL/SQL介绍 2）PL/SQL语句块结构 3）开发和运行环境 4）PL/SQL变量类型 5）变量声明 6）SQL控制结构 7）PL/SQL中的SQL 8）游标 9）错误处理 10）集合 11）创建过程、函数和包 12）创建和管理触发器 3、Oracle Database 10g Administration Workshop I 1）安装Oracle Database 10G软件 2）创建Oracle数据库 3）数据库接口 4）控制数据库 5）存储结构 6）管理用户 7）管理模式对象 8）管理数据 9）PL/SQL 10）Oracle 数据库安全性 11）Oracle Net Services 12）Oracle 共享服务器 13）性能监控 14）积极维护：目标 15）监控和管理撤消 16）监控和解决锁冲突 17）备份和恢复的概念 18）数据库备份 19）数据库恢复 4、Oracle Database 10g Administration Workshop II 1）使用全球化支持 2）确保Oracle 监听程序的安全性 3）启用分布式事务处理 4）配置Recovery Manager 5）使用Recovery Manager 6）诊断来源 7）从非关键性数据丢失中恢复 8）不完全恢复 9）闪回数据库 10）从用户错误中恢复 11）处理数据库损坏 12）优化Oracle 数据库 13）实施物化图 14）管理资源 15）使用计划程序自动执行任务 16）高级工具和技术 5、Oracle Database 10g 数据库Java开发 1）利用JDeveloper 10g进行工程开发 2）JDeveloper 10g中利用JDBC访问数据 库实例 3）利用JDeveloper 10g开发SQL、PL/SQL 和SQLJ 4）利用Java开发存储过程 5）基于JDeveloper 10g的Web应用开发 六、J2EE高级开发技术 1、UML 2、Webservice 3、J2EE设计模式 4、Weblogic开发 5、Jboss开发 6、会话Bean 7、实体Bean 8、消息Bean 9、J2EE服务 10、AJAX
总结ORACLE系统视图及表大全： dba_开头..... dba_users 数据库用户信息 dba_segments 表段信息 dba_extents 数据区信息 dba_objects 数据库对象信息 dba_tablespaces 数据库表空间信息 dba_data_files 数据文件设置信息 dba_temp_files 临时数据文件信息 dba_rollback_segs 回滚段信息 dba_ts_quotas 用户表空间配额信息 dba_free_space数据库空闲空间信息 dba_profiles 数据库用户资源限制信息 dba_sys_privs 用户的系统权限信息 dba_tab_privs用户具有的对象权限信息dba_col_privs用户具有的列对象权限信息dba_role_privs用户具有的角色信息 dba_audit_trail审计跟踪记录信息 dba_stmt_audit_opts审计设置信息 dba_audit_object 对象审计结果信息 dba_audit_session会话审计结果信息 dba_indexes用户模式的索引信息 user_开头 user_objects 用户对象信息 user_source 数据库用户的所有资源对象信息user_segments 用户的表段信息 user_tables 用户的表对象信息 user_tab_columns 用户的表列信息 user_constraints 用户的对象约束信息 user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息 user_col_privs 当前用户的表列权限信息 user_role_privs 当前用户的角色权限信息 user_indexes 用户的索引信息 user_ind_columns用户的索引对应的表列信息 user_cons_columns 用户的约束对应的表列信息 user_clusters 用户的所有簇信息 user_clu_columns 用户的簇所包含的内容信息 user_cluster_hash_expressions 散列簇的信息 v$开头 v$database 数据库信息 v$datafile 数据文件信息 v$controlfile控制文件信息 v$logfile 重做日志信息 v$instance 数据库实例信息 v$log 日志组信息 v$loghist 日志历史信息 v$sga 数据库SGA信息 v$parameter 初始化参数信息 v$process 数据库服务器进程信息 v$bgprocess 数据库后台进程信息 v$controlfile_record_section 控制文件记载的各部分信息v$thread 线程信息 v$datafile_header 数据文件头所记载的信息 v$archived_log归档日志信息 v$archive_dest 归档日志的设置信息 v$logmnr_contents 归档日志分析的DML DDL结果信息v$logmnr_dictionary 日志分析的字典文件信息 v$logmnr_logs 日志分析的日志列表信息 v$tablespace 表空间信息
Oracle 处理外部表错误 在将数据文件中的数据转换为表中列数据时，不可避免会现一些错误。当出现错误时，用户就需要收集错误信息，从中找到导致出现错误的原因并加以纠正。在创建外部表时，关于错误处理的子句包括：REJECT LIMIT、BADFILE和LOGFILE子句。 1．REJECT LIMIT子句 如果在创建外部表时，使用了REJECT LIMIT子句，则在将数据文件中的数据转换为表定义的列数据期间，数据库将允许出现特定数量的错误。如果用户在执行一个查询，而Oracle遇到了超过这个转换数量的错误，那么查询将会失败。 在默认情况下，REJECT LIMIT子句指定的数值为0。可以使用REJECT LIMIT子句设置允许出现的错误数为UNLIMITED，这样查询就不会失败。如果外部数据文件中的所有记录都由于转换错误而失败，那么查询这个外部表时将返回0行。 例如，在下面的示例中将重新创建的外部表EMPLOYEES_EXTERNAL，并在指定字段分隔符时使用分号“;”，很显然这在转换数据时将发生错误。但是，由于在其中使用了REJECT LIMIT子句指定错误数为无限UNLIMITED，所以要成功创建外部表： SQL> create table ext_emp 2 ( … ) 10 organization external 11 (type oracle_loader 12 default directory exterior_data 13 access parameters( 14 records delimited by newline 15 fields terminated by ',') 16 location('employees.csv')); 17 reject limit unlimited; 表已创建。 虽然忽略了在创建外部表时发生的错误，但是，这意味着Oracle可能无法读取数据文件中的数据。如果这时用户使用SELECT语句查询该外部表，则无法返回正常的数据。 2．BADFILE和NOBADFILE子句 当读取外部表的数据文件时，数据库可能会遇到数据类型转换错误，不能够将源文件转换成数据库中为外部表定义的列。这时可以在创建外部表时使用了BADFILE子句，将所有不能转换的数值都会被写入BADFILE指定的文件中。 SQL> create table ext_emp 2 (empno number(4), … 9 deptno number(3)) 10 organization external 11 (type oracle_loader 12 default directory exterior_data
Windows中使用Oracle外部表导入数据 Oracle 数据库的外部表，是指不在数据库中的表，也称“虚表”，对外部表可以使用标准SQL 语句进行装载，而不需要先将外部表中的数据装载进数据库中。 只读：由于外部表实际上是磁盘中的文件，所以不能通过SQL语句对外部表文件进行修改。对于外部表，实际使用中可以方便的用来加载数据，比如初始化用户数据，或者将历史数据报表使用外部表加载用来作相关临时性分析，而不需要程序来提供导入支持。 闲话少说，按照下面的例子操练之后，相信大家都掌握外部表的使用原理，在具体使用中融会贯通。（在linux中的使用方法在此文中不作描述，原理相同） --创建目录 create directory test_dir as'E:\external_table'; --sys登录，授权目录给指定用户 grant read,write on directory test_dir to test_user; --使用被授权的用户test_user创建外部表: create table test_table (empno int, ename varchar2(20), sal int, deptno int) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY test_dir ACCESS Parameters ( RECORDS DELIMITED BY NEWLINE badfile 'bad_test.txt' LOGFILE'log_test.txt' FIELDS TERMINATED BY',' MISSING FIELD VALUES ARE NULL (empno,ename,sal,deptno) ) LOCATION('t1.txt') ) ; --查询外部表 select * from test_table; --查看外部表的两个字典视图 select * from dba_external_tables; select * from dba_external_locations; SELECT OWNER, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_PARAMETERS
关于oracle自带的表***********************************8 emp： empno：员工编号；ename：员工名字；job：员工工种；mgr: 上司；hiredate：入职时间；sal：基本工资；comm：补贴；deptno：所属部门编号； dept： deptno：部门编号；dname：部门名称；loc：地理位置； salgrade： grade：工资等级；losal：最低限额；hisal：最高限额； dual： 系统自带的一张空表；可用于计算数据：select 2*3 from dual; **sql_function1********************************************************** select lower(ename) from emp; 取出的名字全部变成小写。 select ename from emp where lower(ename) like '_a%';取出的名字变成小写后不含字母a select substr(ename, 2, 3) from emp;从第二个字符截，截取三个字符。 select cha(65) from dual; 将数字转化为字符（显示为a）。 select ascii('A') from dual; 将字符转化为数字。 select round(23.652) from dual; (显示24) select round(23.652, 2) from dual; （显示23.65） select round(23.652, -1) from dual; （显示20） select to_char(sal, '$99,999.9999')from emp;强制转化为指定的格式。 select to_char(sal, 'L0000.0000')from emp;同上。 select to_char(hiredate, YYYY-MM-DD HH:MI:SS) from emp; 对时间格式显示处理。 select to_char(sysdate, YYYY-MM-DD HH:MI:SS) from emp; 12进制。 select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from emp; 24进制。 *********************************************************************** **sql_function2******************************************************* select ename, hiredate from emp where hiredate > to_date('1981-2-20' 12:34:52, 'YYYY-MM-DD HH24:MI:SS'); 函数to_date 将字符转化为时间格式。 select sal from emp where sal > to_number('$1,250.00', '$9,999.99'); 函数to_number将字符转化为数字格式，以作比较。 select ename sal*12 + nvl(comm 0) from emp; 函数nvl作用为当comm为null的时候当作处理，避免了comm为null给结果带来的不便。
使用Oracle的外部表查询警告日志文件 从Oracle9i开始，Oracle的外部表技术（Oracle External Tables）被极大的增强，通过外部表访问外部数据增强了Oracle数据库和外部数据源进行数据交互的能力，对于数据仓库和ETL来说，这些增强极大的方便了数据访问。 对于DBA来说，最常见一个例子是可以使用外部表来访问警告日志文件或其他跟踪文件. 以下一个例子用来说明外部表的用途。 首先需要创建一个Directory： [oracle@jumper oracle]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sun Oct 15 21:42:28 2006 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> create or replace directory bdump 2 as '/opt/oracle/admin/eygle/bdump'; Directory created. SQL> col DIRECTORY_PATH for a30 SQL> col owner for a10 SQL> select * from dba_directories;
查看表：user_tables、all_tables、dba_tables 查看表字段：user_tab_columns、all_ tab_columns、dba_tab_columns 查看表注释：user_ tab_comments 、all_tab_comments、dba_tab_comments 查看字段注释：user_col_comments、all_col_comments、dba_col_comments 查看索引信息：user_indexes、all_indexes、dba_indexes 查看索引所在字段：user_ind_columns、all_ind_columns、dba_ind_columns 查看约束信息：user_constraints、all_constraints、dba_constraints 查看约束所在字段：user_cons_columns、all_cons_columns、dba_cons_columns 查看触发器信息：user_triggers、all_triggers、all_triggers 查看序列信息：user_sequences、all_sequences、dba_sequences 查看视图信息：user_views、all_views、dba_views 查看同义词信息：user_synonyms、all_synonyms、dba_synonyms 查看DBLINK信息：user_db_links、all_db_links、dba_db_links 查看JOB信息：user_jobs、all_jobs、dba_jobs 查看所有对象信息（过程PROCEDURE、函数FUNCTION、包和包体、JOB和LOB大字段、表、视图、索引、序列、触发器）：user_objects、all_objects、dba_objects 查看过程、函数、触发器、包和包体内容：user_source、all_source、dba_source
使用ORACLE连接HDFS有个专项的解决方案[O]racle [D]irect [C]onnector for [H]adoop Distributed Files System，简称ODCH。 该软件包可以到Oracle官网下载：https://www.doczj.com/doc/4e15311307.html,/technetwork/bdc/big-data-connectors/downloads/index.html 选择第一项：Oracle SQL Connector for Hadoop Distributed File System Release 2.1.0 我们这里使用2.1版本，下载到安装包后，解压即可： [root@ora11g ~]# unzip oraosch-2.1.0.zip 变态呀，压缩包里还有压缩包： [root@ora11g ~]# unzip orahdfs-2.1.0.zip -d /usr/local/ [root@ora11g ~]# chown -R oracle install /usr/local/orahdfs-2.1.0 在解压缩文件的bin中，hdfs_stream有两个环境变量需要手动指定，编辑该文件： [root@ora11g ~]# vi /usr/local/orahdfs-2.1.0/bin/hdfs_stream 增加两行环境变量： OSCH_HOME=/usr/local/orahdfs-2.1.0 HADOOP_HOME=/usr/local/hadoop-0.20.2 再次编辑grid用户环境变量： [root@ora11g ~]$ vi /home/grid/.bash_profile 增加下列内容： export DIRECTHDFS_HOME=/usr/local/orahdfs-2.1.0 export OSCH_HOME=${DIRECTHDFS_HOME} export ORAHDFS_JAR=${DIRECTHDFS_HOME}/jlib/orahdfs.jar export HDFS_BIN_PATH=${DIRECTHDFS_HOME}/bin 以oracle用户身份登录，创建两个目录，分别用于保存ODCH的操作日志和位置文件(location files)： [oracle@ora11g ~]$ mkdir /data/ora11g/ODCH/{logs,extdir} -p [oracle@ora11g ~]$ chmod -R 777 /data/ora11g/ODCH/ 而后以sysdba身份登录到ORACLE，创建3个directory对象： SQL> create or replace directory ODCH_LOG_DIR as '/data/ora11g/ODCH/logs'; grant read, write on directory ODCH_LOG_DIR to SCOTT; create or replace directory ODCH_DATA_DIR as '/data/ora11g/ODCH/extdir'; grant read, write on directory ODCH_DATA_DIR to SCOTT; create or replace directory HDFS_BIN_PATH as '/usr/local/orahdfs-2.1.0/bin'; grant read,write,execute on directory HDFS_BIN_PATH to SCOTT; Directory created. SQL> Grant succeeded. SQL> Directory created. SQL> Grant succeeded.