Oracle_系统表大全
数据字典dict总是属于Oracle用户sys的。
1、用户：select username from dba_users;改口令alter user spgroup identified by spgtest;2、表空间：select * from dba_data_files;select * from dba_tablespaces;//表空间select tablespace_name,sum(bytes), sum(blocks)from dba_free_space group by tablespace_name;//空闲表空间select * from dba_data_fileswhere tablespace_name='RBS';//表空间对应的数据文件select * from dba_segmentswhere tablespace_name='INDEXS';3、数据库对象：select * from dba_objects;CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PAC KAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
4、表：select * from dba_tables;analyze my_table compute statistics;->dba_tables后6列select extent_id,bytes from dba_extentswhere segment_name='CUSTOMERS' and segment_type='TABLE'order by extent_id;//表使用的extent的信息。
segment_type='ROLLBACK'查看回滚段的空间分配信息列信息：select distinct table_namefrom user_tab_columnswhere column_name='SO_TYPE_ID';5、索引：select * from dba_indexes;//索引，包括主键索引select * from dba_ind_columns;//索引列select i.index_name,i.uniqueness,c.column_namefrom user_indexes i,user_ind_columns cwhere i.index_name=c.index_nameand i.table_name ='ACC_NBR';//联接使用6、序列：select * from dba_sequences;7、视图：select * from dba_views;select * from all_views;text 可用于查询视图生成的脚本8、聚簇：select * from dba_clusters;9、快照：select * from dba_snapshots;快照、分区应存在相应的表空间。
10、同义词：select * from dba_synonymswhere table_owner='SPGROUP';//if owner is PUBLIC,then the synonyms is a public synonym.if owner is one of users,then the synonyms is a private synonym.11、数据库链：select * from dba_db_links;在spbase下建数据库链create database link dbl_spnewconnect to spnew identified by spnew using 'jhhx';insert into acc_nbr@dbl_spnewselect * from acc_nbr where nxx_nbr='237' and line_nbr='8888';12、触发器：select * from dba_trigers;存储过程，函数从dba_objects查找。
其文本：select text from user_source where name='BOOK_SP_EXAMPLE';建立出错：select * from user_errors;oracle总是将存储过程，函数等软件放在SYSTEM表空间。
13、约束：（1）约束是和表关联的，可在create table或alter table table_name add/drop/modify 来建立、修改、删除约束。
可以临时禁止约束，如：alter table book_exampledisable constraint book_example_1;alter table book_exampleenable constraint book_example_1;（2）主键和外键被称为表约束，而not null和unique之类的约束被称为列约束。
通常将主键和外键作为单独的命名约束放在字段列表下面，而列约束可放在列定义的同一行，这样更具有可读性。
（3）列约束可从表定义看出，即describe;表约束即主键和外键，可从dba_constraints和dba_cons_columns 查。
select * from user_constraintswhere table_name='BOOK_EXAMPLE';select owner,CONSTRAINT_NAME,TABLE_NAMEfrom user_constraintswhere constraint_type='R'order by table_name;（4）定义约束可以无名（系统自动生成约束名）和自己定义约束名（特别是主键、外键）如：create table book_example(identifier number not null);create table book_example(identifier number constranit book_example_1 not null);14、回滚段：在所有的修改结果存入磁盘前，回滚段中保持恢复该事务所需的全部信息，必须以数据库发生的事务来相应确定其大小（DML语句才可回滚，create,drop,truncate等DDL不能回滚）。
回滚段数量=并发事务/4，但不能超过50；使每个回滚段大小足够处理一个完整的事务；create rollback segment r05tablespace rbs;create rollback segment rbs_cvttablespace rbsstorage(initial 1M next 500k);使回滚段在线alter rollback segment r04 online;用dba_extents,v$rollback_segs监测回滚段的大小和动态增长。
回滚段的区间信息select * from dba_extentswhere segment_type='ROLLBACK' and segment_name='RB1';回滚段的段信息,其中bytes显示目前回滚段的字节数select * from dba_segmentswhere segment_type='ROLLBACK' and segment_name='RB1';为事物指定回归段set transaction use rollback segment rbs_cvt针对bytes可以使用回滚段回缩。
alter rollback segment rbs_cvt shrink;select bytes,extents,max_extents from dba_segmentswhere segment_type='ROLLBACK' and segment_name='RBS_CVT'; 回滚段的当前状态信息：select * from dba_rollback_segswhere segment_name='RB1';比多回滚段状态status，回滚段所属实例instance_num查优化值optimalselect ,s.optsizefrom v$rollname n,v$rollstat swhere n=n;回滚段中的数据set transaction use rollback segment rb1;/*回滚段名*/select ,s.writesfrom v$rollname n,v$rollstat swhere n=n;当事务处理完毕，再次查询$rollstat，比较writes(回滚段条目字节数)差值，可确定事务的大小。
查询回滚段中的事务column rr heading 'RB Segment' format a18column us heading 'Username' format a15column os heading 'Os User' format a10column te heading 'Terminal' format a10select rr,nvl(ername,'no transaction') us,s.osuser os,s.terminal tefrom v$lock l,v$session s,v$rollname rwhere l.sid=s.sid(+)and trunc(l.id1/65536)=Nand l.type='TX'and l.lmode=6order by ;15、作业查询作业信息select job,broken,next_date,interval,what from user_jobs;select job,broken,next_date,interval,what from dba_jobs;查询正在运行的作业select * from dba_jobs_running;使用包exec dbms_job.submit(:v_num,'a;',sysdate,'sysdate + (10/(24*60*60))')加入作业。
OracleEBS常用表和视图
OracleEBS常用表和视图fnd_user系统用户表fnd_application应用信息表FND_PROFILE_OPTIONS_VL 系统配置文件fnd_menus 菜单fnd_menu_entries_tlFND_NEW_MESSAGES消息表FND_FORM表单表FND_CONCURRENT_PROGRAMS_VL 并发程序视图FND_CONCURRENT_PROGRAMS_TLFND_CONCURRENT_PROGRAMSFND_DESCR_FLEX_COL_USAGE_VLFND_DESCR_FLEX_COL_USAGE_TLFND_DESCR_FLEX_COLUMN_USAGESFND_EXECUTABLES_FORM_V 可执行并发程序视图FND_EXECUTABLES_TLFND_EXECUTABLESFND_DESCRIPTIVE_FLEXSFND_CONC_REQ_SUMMARY_V 并发请求视图FND_CONCURRENT_REQUESTSFND_RESPONSIBILITY职责表FND_RESPONSIBILITY_VL 职责FND_USER_RESP_GROUPS 用户职责fnd_flex_value_sets值集表FND_FLEX_VALUESFND_IREP_ALL_INTERFACES 接口表FND_IREP_CLASSESFnd_Irep_Classes_Tlfnd_territories_vl 国家视图fnd_log_messages 日志表fnd_form_functions 功能FND_DOCUMENT_SEQUENCES 单据序列FND_DOC_SEQUENCE_ASSIGNMENTS 序列分配fnd_id_flexs 关键弹性域定义表FND_ID_FLEX_STRUCTURES 弹性域结构表FND_ID_FLEX_SEGMENTS 弹性域段表fnd_descriptive_flexs 描述性弹性域属性表FND_DESCR_FLEX_CONTEXTS 弹性域列类别表FND_DESCR_FLEX_COLUMN_USAGES 弹性域列类别属性表FND_FLEX_VALUE_SETS 值集表FND_FLEX_VALUES 值表Fnd_Flex_Values_Tl 值描述表FND_FLEX_VALUE_NORM_HIERARCHY 值层次关系表FND_FORM_CUSTOM_RULES 个性化视图fnd_profile_options_tl 配置文件------------------------------------------------------------------------------------------------------PER_ALL_PEOPLE_F员工表per_people_f员工视图MTL_PLANNERS计划员表po_buyers_all_v采购员视图org_organization_definitions 库存组织视图hr_operating_units OU视图cux_wip_location_sign 位置号表HCM_PRODUCTION_LINE 生产线车间表user_objectsALL_SOURCEALL_PROCEDURES 存储过程wf_local_user_rolesad_bugs 补丁ad_applied_patchesXDO_TEMPLATES_B 模板XDO_TEMPLATES_TL------------------------------------------------------------------------------------------------po_vendors供应商信息表po_vendor_sites_all供应商地点信息视图AP_SUPPLIER_SITES_ALL 供应商地点表po_vendor_contacts------------------------------------------------------------------------------------------------------------------------------------------------wip_entities作业名信息表wip_discrete_jobs离散作业表wip_requirement_operations作业名物料需求发放表WIP_ACCOUNTING_CLASSES 工单分类表-------------------------------------------------------------------------------------------------------------------hr_locations交货地点hr_all_organization_unitsrcv_transactions_interface接收事务接口表po_interface_errors 接口错误信息表（pie.interface_line_id = rti.interface_transaction_id）po_requisitions_interface_all 请购接口表po_requisition_headers_all 请购单头信息po_requisition_lines_all请购单行信息po_req_distributions_all请购单分配行po_headers_all 采购订单头表po_lines_all 采购订单行表po_line_locations_all采购发运表po_distributions_all分配po_releases_all发放rcv_shipment_headers接收发送头表rcv_shipment_lines接收发送行表rcv_transactions接收交易表rcv_receiving_sub_ledger暂记应付表mtl_material_transactions物料事务处理mtl_transaction_types物料事务处理类型RCV_SUPPLYpo_buyers_all_v 采购员视图PO_AGENTS采购员表------------------------------------------------------------------------------------------------------------------------------------------------bom_bill_of_materials物料清单表bom_inventory_components物料清单构成表bom_departments 部门表bom_operational_routings 工艺路线头表BOM_OPERATION_SEQUENCES 工艺路线行表bom_operation_resources资源表------------------------------------------------------------------------------------------------------------------------------------------MTL_CATEGORY_SET_VALID_CATS_V 物料类别视图mtl_categories_b_kfv 物料类别视图(不含物料类别集)mtl_category_set_valid_cats 物料类别集表mtl_item_categories物料与类别对应表mtl_categories_b物料类别表mtl_system_items_b物料主表MTL_SYSTEM_ITEMS_TLmtl_item_sub_defaults默认接收子库mtl_secondary_inventories子库表mtl_onhand_quantities库存数据表MTL_ONHAND_QUANTITIES_DETAIL 库存现有量表MTL_SECONDARY_INVENTORIES 仓库表mtl_item_locations 项目货位表mtl_material_transactions 出入库记录表mtl_transaction_types事务处理类型表MTL_TXN_REQUEST_HEADERS 物料搬运单头表MTL_TXN_REQUEST_LINES物料搬运单行表mtl_supply 供应表mtl_demand 需求表mtl_parameters 组织参数cst_item_costs物料成本cst_cost_types成本类型MTL_SERIAL_NUMBERS序列号MTL_MATERIAL_STATUSES_TL序列号状态MFG_LOOKUPS序列号状态（SERIAL_NUM_STATUS）------------------------------------------------------------------------------------------------------------------------hr_organization_units 组织及库存组织视图hz_parties 客户表hz_relationships客户关系表HZ_PARTY_SITES 客户地点表HZ_LOCATIONS地点表hz_cust_accounts 客户账户表HZ_CUST_ACCT_SITES_ALL 客户地点账户表HZ_CUST_SITE_USES_ALL 客户地点业务目的表HZ_CUSTOMER_PROFILES 账户配置文件表HZ_CUST_PROFILE_AMTS 账户配置文件金额表HZ_CONTACT_POINTS联系方式表HZ_CODE_ASSIGNMENTS 客户的税表HZ_CUSTOMER_PROFILES 滞纳费用表---------------------------------------------------------------------------------------------wsh_trips 发运记录表wsh_trip_stops 发运停靠站记录表wsh_new_deliveries 交付记录wsh_delivery_legswsh_delivery_details 交付表wsh_delivery_assignments----------------------------------------------------------------------------------------mrp_sourcing_rules来源补充规则mrp_sr_receipt_orgmrp_sr_source_orgmrp_sr_assignmentsMRP_ASSIGNMENT_SETS来源规则分配集表po_approved_supplier_list批准的供应商列表PO_ASL_ATTRIBUTES批准的供应商列表属性-------------------------------------------------------------------------------------------------------------------------------------------------ENG_CHANGE_STATUSES_VL E CO状态pjm_seiban_numbers内外销标识表pa_projects_allmrp_designatorsDRP名称mrp_forecast_designators预测名称表mrp_forecast_dates预测表mrp_forecast_updates预测更改mrp_gross_requirements毛需求mrp_item_purchase_ordersMRP采购计划mrp_item_wip_entitiesMRP项目离散作业mrp_plans计划表mrp_recommendations计划建议mrp_workbench_bucket_datesMTL_SUPPLY_DEMAND_TEMP 物料供应需求-------------------------------------------------------------------------------------------------------------------------------------------MTL_TRANSACTION_FLOW_HEADERS公司间事务处理流mtl_transaction_flow_lines_v公司间事务处理流节点MTL_INTERCOMPANY_PARAMETERS_V 公司间关系QP_LIST_HEADERS_B价目表QP_LIST_HEADERS_TL价目表Qp_List_LinesQp_Pricing_Attributes--------------------------------------------------------------------------------------------------------------------------------------------------MTL_UNITS_OF_MEASURE单位MTL_UNITS_OF_MEASURE_TLMTL_UOM_CLASSES单位分类mtl_uom_classes_tlMTL_UOM_CONVERSIONS单位换算标准MTL_UOM_CLASS_CONVERSIONS单位换算分类间--------------------------------------------------------------------------------------------------------------------------------------------------AP_INVOICES_ALL 发票头AP_INVOICE_LINES_ALL 发票行xla_ae_headers 会计分录头xla_ae_lines 会计分录行--------------------------------------------------------------------------------------------------------------oe_system_parameters_allOM 系统参数ak_web_user_sec_attr_valuesORG_ACCT_PERIODS库存会计期间GL_PERIODS总账会计期间ZX_RATES_B税率代码ZX_PARTY_TAX_PROFILE供应商的税的配置文件xla_event_types_tlxla_event_classes_tl-------------------------------------------------------------------------------------------------------------------ap_suppliers 供应商头信息ap_supplier_sites_all 供应商地点信息hz_code_assignments 供应商税率zx_party_tax_profile 供应商税信息iby_ext_bank_accounts 供应商银行账号iby_ext_bank_branches_v 供应商银行支行iby_ext_banks_v 供应商银行iby_account_owners 银行账户的拥有者iby_pmt_instr_uses_all 银行账户所在的地点ap_supplier_contacts 供应商联系人hz_contact_points 供应商联系人电话等信息pos_supp_prof_ext_b 供应商地点附加信息iby_external_payees_all 供应商付款信息。
推荐下载-Oracle系统表大全 精品
数据字典dict总是属于Oracle用户sys的。
1、用户：select username from dba_users;改口令alter user spgroup identified by spgtest;2、表空间：select * from dba_data_files;select * from dba_tablespaces;//表空间select tablespace_name,sum(bytes), sum(blocks)from dba_free_space group by tablespace_name;//空闲表空间select * from dba_data_fileswhere tablespace_name='RBS';//表空间对应的数据文件select * from dba_segmentswhere tablespace_name='INDEXS';3、数据库对象：select * from dba_objects;CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PAC KAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
4、表：select * from dba_tables;analyze my_table pute statistics;->dba_tables后6列select extent_id,bytes from dba_extentswhere segment_name='CUSTOMERS' and segment_type='TABLE'order by extent_id;//表使用的extent的信息。
segment_type='ROLLBACK'查看回滚段的空间分配信息列信息：select distinct table_namefrom user_tab_columnswhere column_name='SO_TYPE_ID';5、索引：select * from dba_indexes;//索引，包括主键索引select * from dba_ind_columns;//索引列select i.index_name,i.uniqueness,c.column_namefrom user_indexes i,user_ind_columns cwhere i.index_name=c.index_nameand i.table_name ='ACC_NBR';//联接使用6、序列：select * from dba_sequences;7、视图：select * from dba_views;select * from all_views;text 可用于查询视图生成的脚本8、聚簇：select * from dba_clusters;9、快照：select * from dba_snapshots;快照、分区应存在相应的表空间。
OracleERP表结构
表名: inv.mtl_ 表名: inv.mtl_manufacturers 说明: 说明: 制造商代码表
MANUFACTURER_ID MANUFACTURER_NAME NUMBER VARCHAR2(30)
表名：inv.mtl_item_ 表名：inv.mtl_item_status 说明： 说明：项目状态表
INVENTORY_ITEM_STATUS_CODE DESCRIPTION VARCHAR2(10) VARCHAR2(240)
表名: inv.mtl_picking_ 表名: inv.mtl_picking_rules 说明: 说明: 捡料规则表
PICKING_RULE_ID PICKING_RULE_NAME DESCRIPTION NUMBER VARCHAR2(30) VARCHAR2(50)
A
A
A A
AUTO_SERIAL_ALPHA_PREFIX CYCLE_COUNT_ENABLED_FLAG INVENTORY_ITEM_FLAG LOCATION_CONTROL_CODE LOT_CONTROL_CODE MTL_TRANSACTIONS_ENABLED_FLAG POSITIVE_MEASUREMENT_ERROR RESERVABLE_TYPE RESTRICT_LOCATORS_CODE REVISION_QTY_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE SHELF_LIFE_CODE SHELF_LIFE_DAYS START_AUTO_LOT_NUMBER START_AUTO_SERIAL_NUMBER STOCK_ENABLED_FLAG UNIT_VOLUME UNIT_WEIGHT VOLUME_UOM_CODE WEIGHT_UOM_CODE CARRYING_COST FIXED_DAYS_SUPPLY FIXED_LOT_MULTIPLIER FIXED_ORDER_QUANTITY INVENTORY_PLANNING_CODE MAXIMUM_ORDER_QUANTITY MAX_MINMAX_QUANTITY MINIMUM_ORDER_QUANTITY MIN_MINMAX_QUANTITY MRP_SAFETY_STOCK_CODE MRP_SAFETY_STOCK_PERCENT ORDER_COST PLANNER_CODE SAFETY_STOCK_BUCKET_DAYS SOURCE_ORGANIZATION_ID SOURCE_SUBINVENTORY SOURCE_TYPE ACCEPTABLE_EARLY_DAYS AUTO_REDUCE_MPS DEMAND_TIME_FENCE_CODE DEMAND_TIME_FENCE_DAYS MRP_CALCULATE_ATP_FLAG MRP_PLANNING_CODE OVERRUN_PERCENTAGE PLANNING_MAKE_BUY_CODE PLANNING_TIME_FENCE_CODE PLANNING_TIME_FENCE_DAYS REPETITIVE_PLANNING_FLAG SHRINKAGE_RATE CUMULATIVE_TOTAL_LEAD_TIME CUM_MANUFACTURING_LEAD_TIME FULL_LEAD_TIME
Oracle系统表
Oracle系统表新增字段alter table mytable add f3 number(3,0) default 0 not null;alter table T_LAB_PaperTestRecords add (FWATERS NUMBER(28,10),FQUANTITATIV NUMBER(28,10));性能监控之监控SQL语句分析表analyze table tablename compute statistics for all indexes;analyze table tablename compute statistics for all indexed columns;analyze table tablename compute statistics for table;监控事例的等待select event,sum(decode(wait_Time,0,0,1)) "Prev",sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"from v$session_Waitgroup by event order by 4;查看碎片程度高的表SELECT segment_name table_name , COUNT(*) extentsFROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX( COUNT(*) FROM dba_segments GROUP BY segment_name);表、索引的存储情况检查select segment_name,sum(bytes),count(*) ext_quan fromdba_extents wheretablespace_name=&tablespace_name and segment_type=TABLE group by tablespace_name,segment_name;select segment_name,count(*) from dba_extents where segment_type=INDEX and owner=&ownergroup by segment_name;找使用CPU多的用户session12是cpu used by this sessionselect a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;监控表空间的 I/O 比例select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbwfrom v$filestat f, dba_data_files dfwhere f.file# = df.file_idorder by df.tablespace_name;回滚段的争用情况select name, waits, gets, waits/gets "Ratio"from v$rollstat a, v$rollname bwhere/doc/a1d1093710661ed9ac51f304.html n = /doc/a1d1093710661ed9ac51f304.html n;在某个用户下找所有的索引select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;监控文件系统的 I/O 比例select substr(a.file#,1,2) "#", substr(/doc/a1d1093710661ed9ac51f304. html ,1,30) "Name",a.status, a.bytes,b.phyrds, b.phywrtsfrom v$datafile a, v$filestat bwhere a.file# = b.file#;监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"from v$rowcachewhere gets+getmisses <>0group by parameter, gets, getmisses;监控 SGA 中共享缓存区的命中率，应该小于1％select sum(pins) "T otal Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"from v$librarycache;监控 SGA 的命中率select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 38 and b.statistic# = 39and c.statistic# = 40;监控 SGA 中重做日志缓存区的命中率，应该小于1％SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*10 0) ratio2FROM v$latch WHERE name IN (redo allocation, redo copy);显示所有数据库对象的类别和大小select count(name) num_instances ,type ,sum(source_size) source_size ,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_requiredfrom dba_object_sizegroup by type order by 2;监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk));监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;监控字典缓冲区SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;后者除以前者,此比率小于1％,接近0％为好。
Oracle中查看所有的表,用户表,列名,主键,外键
在Oracle中查看所有的表:select * from tab/dba_tables/dba_objects/cat;看用户建立的表 :selecttable_name from user_tables; //当前用户的表selecttable_name from all_tables; //所有用户的表selecttable_name from dba_tables; //包括系统表select * from user_indexes //可以查询出所有的用户表索引查所有用户的表在all_tables主键名称、外键在all_constraints索引在all_indexes但主键也会成为索引，所以主键也会在all_indexes里面。
具体需要的字段可以DESC下这几个view，dba登陆的话可以把all换成dba1、查找表的所有索引（包括索引名，类型，构成列）：select t.*,i.index_type from user_ind_columnst,user_indexesi where t.index_nam e = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表2、查找表的主键（包括名称，构成列）：select cu.* from user_cons_columns cu, user_constraints au where cu.constraint _name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表3、查找表的唯一性约束（包括名称，构成列）：selectcolumn_name from user_cons_columns cu, user_constraints au where cu. constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table _name = 要查询的表4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）：select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表查询外键约束的列名：select * from user_cons_columns cl where cl.constraint_name = 外键名称查询引用表的键的列名：select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名5、查询表的所有列及其属性select t.*,MENTS from user_tab_columnst,user_col_comments c where t.t able_name = c.table_name and t.column_name = c.column_name and t.table_na me = 要查询的表。
Oracle系统表大全
数据字典dict总是属于Oracle用户sys的。
、用户：select username from dba_users;改口令alter user spgroup identified by spgtest;2、表空间：select * from dba_data_files;select * from dba_tablespaces;//表空间select tablespace_name,sum(bytes), sum(blocks)from dba_free_space group by tablespace_name;//空闲表空间select * from dba_data_fileswhere tablespace_name='RBS';//表空间对应的数据文件select * from dba_segmentswhere tablespace_name='INDEXS';3、数据库对象：select * from dba_objects;CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PAC KAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
4、表：select * from dba_tables;analyze my_table compute statistics;>dba_tables后6列select extent_id,bytes from dba_extentswhere segment_name='CUSTOMERS' and segment_type='TABLE'order by extent_id;//表使用的extent的信息。
segment_type='ROLLBACK'查看回滚段的空间分配信息列信息：select distinct table_namefrom user_tab_columnswhere column_name='SO_TYPE_ID';5、索引：select * from dba_indexes;//索引，包括主键索引select * from dba_ind_columns;//索引列select i.index_name,i.uniqueness,c.column_namefrom user_indexes i,user_ind_columns cwhere i.index_name=c.index_nameand i.table_name ='ACC_NBR';//联接使用6、序列：select * from dba_sequences;7、视图：select * from dba_views;select * from all_views;text 可用于查询视图生成的脚本8、聚簇：select * from dba_clusters;9、快照：select * from dba_snapshots;快照、分区应存在相应的表空间。
orcale查找一些系统表的
控制文件：记录数据物理结构的二进制文件。
逻辑组件：由“表空间”，“段”，“区”和“数据块”组成
表空间：相当于SQL2000用“Create Database”创建的一个数据库。
Oracle企业管理器：为管理员提供图形窗口管理实例，模式，安全性和存储等。
四：表空间
System表空间：在每一个数据库都有一个名为SYSTEM的表空间，即系统表空间，该表空间是在创建数据库或数据库安装时自动创建的，用于存储系统的数据字典表，系统程序单元，过程，函数，包和触发器等，也可以用于存储用户表，索引等对象。
filename: 表空间中数据文件的路径和名称。
Size: 指定表空间用于存储数据的文件的大小，“K”表示千字节, “M”表示兆
AutoExtend: 用来启用或禁用数据文件的自动扩展。
示例：创建一个10M的mySpace表空间.
Grant命令可以用来分配权限。
分配系统权限：
Grant Connect To Fly
Grant Resource To Fly
Temporary TableSpace： 为用户指定临时表空间。
注意：创建一个用户时必须为这个用户指定一个默认表空间，改用户将与这个表空间相关联，以后该用户创建表，视图，索引等等都存储在这个表空间中。
注意： 一个用户只能有一个默认表空间，而一个表空间可以有多个用户。
OracleHOME_NAMEHTTPServer： 此服务用户启动Oracle HTTP服务器。
OracleServiceSID： SID是在安装Oracle时输入的数据库名称。 如果此服务没有启动，则数据库客户端应用程序，如SQL*Plus连接到数据库服务器时就会出现错误。
oracle系统表格大全整理
oracle系统表查询 2005-4-29 网络数据字典dict总是属于Oracle用户sys的。
******************************************************************************* *******************************************1、用户：select username from dba_users;创建用户CREATE USER jzmis PROFILE DEFAULT IDENTIFIED BY jzmisDEFAULTTABLESPACE USERS TEMPORARYTABLESPACE TEMP ACCOUNT UNLOCK;GRANT DBA TO jzmis WITH ADMIN OPTION;commit;改口令alter user spgroup identified by spgtest;2、表空间：select * from dba_data_files;select * from dba_tablespaces;//表空间select tablespace_name,sum(bytes), sum(blocks)from dba_free_space group by tablespace_name;//空闲表空间select * from dba_data_fileswhere tablespace_name='RBS';//表空间对应的数据文件select * from dba_segmentswhere tablespace_name='INDEXS';删除表空间drop TABLESPACE XNMIS INCLUDING CONTENTS;创建表空间CREATE TABLESPACE "XNMIS"LOGGINGDATAFILE ‘E:\oracle\product\10.2.0\oradata\jzmis\xnmis.dbf’ SIZE 2048M EXTENTMANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTOAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDCREATE TABLESPACE "XNMIS"LOGGINGDATAFILE 'E:\oracle\product\10.2.0\oradata\jzmis\XNMIS.dbf' SIZE 2048M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;'E:\oracle\admin\cmid\JTAIS.ora' （path)"CTAIS" (表空间名）3、数据库对象：select * from dba_objects;CLUSTER、DATABASE LINK、FUNCTION、INDEX、LIBRARY、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、UNDEFINED、VIEW。
OracleERP常用系统表结构
OracleERP常用系统表结构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行IDREQUISITION_HEADER_ID NUMBER PR头IDLINE_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_IDNUMBER收货人代码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）PARENT_REQ_LINE_ID NUMBER父PR行ID（由拆分而来的PR）NOTE_TO_AGENT VARCHAR2(100)给采购员意见PURCHASING_AGENT_ID NUMBER采购员标识码DOCUMENT_TYPE_CODE VARCHAR2(25)文档类型BLANKET_PO_HEADER_ID NUMBER一揽子订单头标识码BLANKET_PO_LINE_NUM NUMBER一揽子订单栏目CURRENCY_CODE VARCHAR2(15)币种RATE_TYPE VARCHAR2(30)汇率类别RATE_DATE DATE汇率日期RATE NUMBER汇率CURRENCY_UNIT_PRICE NUMBER本币单价SUGGESTED_VENDOR_NAME VARCHAR2(80)建议供应商SUGGESTED_VENDOR_CONTACT VARCHAR2(80)建议供应商联系方式SUGGESTED_VENDOR_PHONE VARCHAR2(20)建议供应商电话UN_NUMBER_ID NUMBER货期标识码（一般与货期天数同）HAZARD_CLASS_ID NUMBER风险类别标识码MUST_USE_SUGG_VENDOR_FLAG VARCHAR2(1)是否必须使用建议供应商REFERENCE_NUM VARCHAR2(25)参考栏目ON_RFQ_FLAG VARCHAR2(1)URGENT_FLAG VARCHAR2(1)紧急标志CANCEL_FLAG VARCHAR2(1)取消标志SOURCE_ORGANIZATION_ID NUMBER来源组织SOURCE_SUBINVENTORY VARCHAR2(10)来源子库DESTINATION_TYPE_CODE VARCHAR2(25)目标类型DESTINATION_ORGANIZATION_ID NUMBER目标组织DESTINATION_SUBINVENTORY VARCHAR2(10)目标子库QUANTITY_CANCELLED NUMBER取消数量CANCEL_DATE DATE取消日期CLOSED_CODE VARCHAR2(25)关闭ATTRIBUTE1行政PR收货人ATTRIBUTE2行政PR收货人电话ATTRIBUTE4行政PR申购人工号ATTRIBUTE5行政PR申购部门ATTRIBUTE6行政PR物品制造商ATTRIBUTE7行政PR物品厂家型号ATTRIBUTE8行政PR大类编码ATTRIBUTE9行政PR小类编码表名: PO.PO_REQ_DISTRIBUTIONS_ALL说明: PR分配明细DISTRIBUTION_ID NUMBER分配的IDREQUISITION_LINE_ID NUMBER PR行的IDSET_OF_BOOKS_ID NUMBER帐簿的IDCODE_COMBINATION_ID NUMBER费用科目IDREQ_LINE_QUANTITY NUMBER分配行的数量ATTRIBUTE14VARCHAR2行政PR的AR AmountATTRIBUTE15VARCHAR2行政PR的AR NumberDISTRIBUTION_NUM NUMBER分配的行号ORG_ID NUMBER经营单位的ID表名: PO.PO_LOOKUP_CODES说明: 检查代码LOOKUP_CODE VARCHAR2(25)检查代码LOOKUP_TYPE VARCHAR2(30)检查类型DISPLAYED_FIELD VARCHAR2(25)显示字段DESCRIPTION VARCHAR2(80)描述表名: PO.PO_LOOKUP_TYPES说明: 检查类型LOOKUP_TYPE VARCHAR2(30)检查类型USER_UPDATABLE_FLAG VARCHAR2(1)用户可否更改DESCRIPTION VARCHAR2(80)描述表名: PO.PO_HEADERS_ALL说明: 采购订单头PO_HEADER_ID NUMBER订单头标识码AGENT_ID NUMBER(9)采购员ID(与HR.PER_PEOPLE_F.PERSON_ID关联) TYPE_LOOKUP_CODE VARCHAR2(25)类型(STANDARD,BLANKET)SEGMENT1 VARCHAR2(20)订单号VENDOR_ID NUMBER供应商标识码VENDOR_SITE_ID NUMBER供应商地址标识码VENDOR_CONTACT_ID NUMBER供应商联系方式标识码SHIP_TO_LOCATION_ID NUMBER发运地点标识码BILL_TO_LOCATION_ID NUMBER付款地点标识码TERMS_ID NUMBER条款标识码SHIP_VIA_LOOKUP_CODE VARCHAR2(25)运输方式FOB_LOOKUP_CODE VARCHAR2(25)口岸代码FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25)运送条款STATUS_LOOKUP_CODE VARCHAR2(25)状态CURRENCY_CODE VARCHAR2(15)币种RATE_TYPE VARCHAR2(30)汇率类型RATE_DATE DATE汇率日期RATE NUMBER汇率FROM_HEADER_ID NUMBER来源订单头标识码FROM_TYPE_LOOKUP_CODE VARCHAR2(25)来源检查类型START_DATE DATE开始时间END_DATE DATE结束时间BLANKET_TOTAL_AMOUNT NUMBER一揽子订单总金额AUTHORIZATION_STATUS VARCHAR2(25)审批状态REVISION_NUM NUMBER版本号REVISED_DATE VARCHAR2(25)修订日期APPROVED_FLAG VARCHAR2(1)批准标志APPROVED_DATE DATE批准日期AMOUNT_LIMIT NUMBER金额限制MIN_RELEASE_AMOUNT NUMBER最小金额NOTE_TO_AUTHORIZER VARCHAR2(240对审批人的说明NOTE_TO_VENDOR VARCHAR2(240对供应商的说明NOTE_TO_RECEIVER VARCHAR2(240对接收者的说明PRINT_COUNT NUMBERPRINTED_DATE DATE制单日期VENDOR_ORDER_NUM VARCHAR2(25)CONFIRMING_ORDER_FLAG VARCHAR2(1)签返(确认)订单标志COMMENTS VARCHAR2(240注释REPLY_DATE DATE回复日期REPLY_METHOD_LOOKUP_CODE VARCHAR2(25)回复方法检查代码RFQ_CLOSE_DATE DATEQUOTE_TYPE_LOOKUP_CODE VARCHAR2(25)报价类型检查代码QUOTATION_CLASS_CODE VARCHAR2(25)报价类代码QUOTE_WARNING_DELAY_UNIT VARCHAR2(25)QUOTE_WARNING_DELAY NUMBERQUOTE_VENDOR_QUOTE_NUMBER VARCHAR2(25)ACCEPTANCE_REQUIRED_FLAG VARCHAR2(1)必须接受标志ACCEPTANCE_DUE_DATE DATE接受截止日期CLOSED_DATE DATE 关闭日期USER_HOLD_FLAG VARCHAR2(1)APPROVAL_REQUIRED_FLAG VARCHAR2(1)必须批准标志CANCEL_FLAG VARCHAR2(1)取消标志FIRM_STATUS_LOOKUP_CODE VARCHAR2(30)FIRM_DATE DATEFROZEN_FLAG VARCHAR2(1)冻结标志CLOSED_CODE VARCHAR2(25)关闭USSGL_TRANSACTION_CODE VARCHAR2(30)GOVERNMENT_CONTEXT VARCHAR2(30)SUPPLY_AGREEMENT_FLAG VARCHAR2(1)供应协议标志表名: PO.PO_LINES_ALL说明: 采购订单行PO_LINE_ID NUMBER行标识码PO_HEADER_ID NUMBER订单头标识码LINE_TYPE_ID NUMBER行类型LINE_NUM NUMBER行栏目ITEM_ID NUMBER项目内码ITEM_REVISION VARCHAR2(3)项目版本CATEGORY_ID NUMBER归类标识码ITEM_DESCRIPTION VARCHAR2(240)项目描述UNIT_MEAS_LOOKUP_CODE VARCHAR2(25)单位QUANTITY_COMMITTED NUMBER提交数量COMMITTED_AMOUNT NUMBER提交金额ALLOW_PRICE_OVERRIDE_FLAG VARCHAR2(1)价格佣金允许NOT_TO_EXCEED_PRICE NUMBER不允许超过价格LIST_PRICE_PER_UNIT NUMBERUNIT_PRICE NUMBER单价QUANTITY NUMBER数量UN_NUMBER_ID NUMBER货期标识码HAZARD_CLASS_IDNUMBER风险类别标识码NOTE_TO_VENDOR VARCHAR2(240)对供应商的说明FROM_HEADER_ID NUMBER来源订单头号FROM_LINE_ID NUMBER来源行号MIN_ORDER_QUANTITY NUMBER最小订单数量MAX_ORDER_QUANTITY NUMBER最大订单数量QTY_RCV_TOLERANCE NUMBER可接收数量OVER_TOLERANCE_ERROR_FLAG VARCHAR2(25)超差示错标志MARKET_PRICE NUMBER市价UNORDERED_FLAG VARCHAR2(1)不定货标志CLOSED_FLAG VARCHAR2(1)关闭标志USER_HOLD_FLAG VARCHAR2(1)CANCEL_FLAG VARCHAR2(1)取消标志CANCELLED_BY NUMBER(9)取消者CANCEL_DATE DATE取消日期CANCEL_REASON VARCHAR2(240)取消原因FIRM_STATUS_LOOKUP_CODE VARCHAR2(30)FIRM_DATE DATETAXABLE_FLAG VARCHAR2(1)应税标志TAX_NAME VARCHAR2(15)税种CAPITAL_EXPENSE_FLAG VARCHAR2(1)大写金额标志NEGOTIATED_BY_PREPARER_FLAG VARCHAR2(1)CLOSED_DATE DATE关闭日期CLOSED_REASON VARCHAR2(240)关闭原因TRANSACTION_REASON_CODE VARCHAR2(25)交易原因ATTRIBUTE1VARCHAR2(150)制造商ATTRIBUTE2VARCHAR2(150)为制造商ATTRIBUTE3VARCHAR2(150)厂家型号ATTRIBUTE4VARCHAR2(150)行类型为一揽子协议为货期ATTRIBUTE5VARCHAR2(150)行类型为一揽子协议为最小订单量ATTRIBUTE6VARCHAR2(150)行类型为一揽子协议为最小包装ATTRIBUTE7VARCHAR2(150)行类型为一揽子协议为分摊比表名: PO.PO_LINE_LOCATIONS_ALL说明: 采购订单定位表LINE_LOCATION_ID NUMBER行定位标识码PO_HEADER_ID NUMBER订单头标识码PO_LINE_ID NUMBER行标识码QUANTITY NUMBER数量QUANTITY_RECEIVED NUMBER接收数量QUANTITY_ACCEPTED NUMBER接受数量QUANTITY_REJECTED NUMBER拒绝数量QUANTITY_BILLED NUMBER付款数量QUANTITY_CANCELLED NUMBER取消数量QUANTITY_SHIPPED NUMBER发运数量UNIT_MEAS_LOOKUP_CODE VARCHAR2(25)单位PO_RELEASE_ID NUMBER一揽子采购协议发放的标识码SHIP_TO_LOCATION_ID NUMBER送货位置SHIP_VIA_LOOKUP_CODE VARCHAR2(25)运输方式NEED_BY_DATE DATE要求到货日期PROMISED_DATE DATE订约日期LAST_ACCEPT_DATE DATE最后接受日期PRICE_OVERRIDE NUMBER佣金价格ENCUMBERED_FLAG VARCHAR2(1)负担标志ENCUMBERED_DATE DATE负担日期UNENCUMBERED_QUANTITY NUMBERFOB_LOOKUP_CODE VARCHAR2(25)FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25)TAXABLE_FLAG VARCHAR2(1)应税标志TAX_NAME VARCHAR2(15)税种ESTIMATED_TAX_AMOUNT NUMBER估计税金FROM_HEADER_ID NUMBER从订单头号FROM_LINE_ID NUMBER从订单行号FROM_LINE_LOCATION_ID NUMBER从定位行号START_DATE DATE开始日期END_DATE DATE结束日期LEAD_TIME NUMBER前导时间LEAD_TIME_UNIT VARCHAR2(25)前导时间单位PRICE_DISCOUNT NUMBER价格折扣TERMS_ID NUMBERAPPROVED_FLAG VARCHAR2(1)批准标志APPROVED_DATE DATE批准日期CLOSED_FLAG VARCHAR2(1)关闭标志CANCEL_FLAG VARCHAR2(1)取消标志CANCELLED_BY NUMBER(9)被谁取消CANCEL_DATE DATE取消日期CANCEL_REASON VARCHAR2(240)取消原因FIRM_STATUS_LOOKUP_CODE VARCHAR2(25)FIRM_DATE DATEUNIT_OF_MEASURE_CLASS VARCHAR2(25)单位ENCUMBER_NOW VARCHAR2(1)INSPECTION_REQUIRED_FLAG VARCHAR2(1)必须检查标志RECEIPT_REQUIRED_FLAG VARCHAR2(1)必须签收标志QTY_RCV_TOLERANCE NUMBER接收差异数量QTY_RCV_EXCEPTION_CODE VARCHAR2(25)接收例外代码ENFORCE_SHIP_TO_LOCATION_CODE VARCHAR2(25)ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1)允许接收替代标志DAYS_EARLY_RECEIPT_ALLOWED NUMBER可提前天数DAYS_LATE_RECEIPT_ALLOWED NUMBER可推后天数RECEIPT_DAYS_EXCEPTION_CODE VARCHAR2(25)接收天数例外代码INVOICE_CLOSE_TOLERANCE NUMBERRECEIVE_CLOSE_TOLERANCE NUMBERSHIP_TO_ORGANIZATION_ID NUMBER送货到组织代码SHIPMENT_NUM NUMBERSOURCE_SHIPMENT_ID NUMBERSHIPMENT_TYPE VARCHAR2(25)送货方式CLOSED_CODE VARCHAR2(25)关闭USSGL_TRANSACTION_CODE VARCHAR2(30)GOVERNMENT_CONTEXT VARCHAR2(30)RECEIVING_ROUTING_ID NUMBERACCRUE_ON_RECEIPT_FLAG VARCHAR2(1)CLOSED_REASON VARCHAR2(240)关闭原因CLOSED_DATE DATE关闭日期ATTRIBUTE1如果是一揽子发放的行则为制造商ATTRIBUTE2如果是一揽子发放的行则为厂家型号表名: PO.PO_DISTRIBUTIONS_ALL 说明: 采购订单分配表PO_DISTRIBUTION_ID NUMBER PO分配的IDPO_HEADER_ID NUMBER PO头的IDPO_LINE_ID NUMBER PO行的IDLINE_LOCATION_ID NUMBER PO交货的IDSET_OF_BOOKS_ID NUMBER财务帐簿CODE_COMBINATION_ID NUMBER费用科目IDQUANTITY_ORDERED NUMBER数量PO_RELEASE_ID NUMBER一揽子采购发放的ID QUANTITY_DELIVERED NUMBER入库数量QUANTITY_BILLED NUMBER开票数量QUANTITY_CANCELLED NUMBER取消数量REQ_HEADER_REFERENCE_NUM VARCHAR2请购单编码REQ_LINE_REFERENCE_NUM VARCHAR2请购单行号REQ_DISTRIBUTION_ID NUMBER PR的分配IDDELIVER_TO_LOCATION_ID NUMBER Ship to地点DELIVER_TO_PERSON_ID NUMBER接收人RATE_DATE DATE汇率日期RATE NUMBER汇率AMOUNT_BILLED NUMBER开票金额DESTINATION_TYPE_CODE VARCHAR2目地类型：库存，费用，外协DESTINATION_ORGANIZATION_ID NUMBER库存组织ID DESTINATION_SUBINVENTORY VARCHAR2子库存ATTRIBUTE14VARCHAR2AR金额ATTRIBUTE15VARCHAR2AR编码WIP_ENTITY_ID NUMBER外协任务IDWIP_OPERATION_SEQ_NUM NUMBER外协任务工序号WIP_RESOURCE_SEQ_NUM NUMBER外协任务资源序号BOM_RESOURCE_ID NUMBER资源的IDDISTRIBUTION_NUM NUMBER PO分配的行号ORG_ID NUMBER经营单位的IDKANBAN_CARD_ID NUMBER看板卡ID表名: PO.PO_RELEASES_ALL说明: 采购发放表PO_RELEASE_ID NUMBER一揽子发放的IDPO_HEADER_ID NUMBER PO头的IDRELEASE_NUM NUMBER一揽子发放的编号AGENT_ID NUMBER采购员IDRELEASE_DATE DATE一揽子发放发放日期CREATION_DATE DATE记录创建日期CREATED_BY NUMBER创建用户的IDREVISION_NUM NUMBER采购发放版本号REVISED_DATE DATE最后一次修改的日期APPROVED_FLAGVARCHAR2已审批标识APPROVED_DATE DATE审批的日期ACCEPTANCE_REQUIRED_FLAG VARCHAR2是否需要签返ACCEPTANCE_DUE_DATE DATE签返的最迟日期HOLD_BY NUMBER暂挂用户的IDHOLD_DATE DATE暂挂的日期HOLD_REASON VARCHAR2暂挂的原因HOLD_FLAG VARCHAR2是否暂挂的标识CANCEL_FLAG VARCHAR2是否取消标识CANCELLED_BY NUMBER取消用户的IDCANCEL_DATE DATE取消的日期CANCEL_REASON VARCHAR2取消的原因FIRM_STATUS_LOOKUP_CODE VARCHAR2是否固定订单FIRM_DATE DATE固定的日期AUTHORIZATION_STATUS VARCHAR2一揽发放的状态CLOSED_CODE VARCHAR2关闭代码FROZEN_FLAG VARCHAR2冻结标识RELEASE_TYPE VARCHAR2发放的类型：计划或一揽子NOTE_TO_VENDOR VARCHAR2供应商通知ORG_ID NUMBER经营单位的IDWF_ITEM_TYPE VARCHAR2工作流的TYPEWF_ITEM_KEY VARCHAR2工作流的KEY表名: PO.PO_UN_NUMBERS说明: 货期表UN_NUMBER_ID NUMBER货期标识码UN_NUMBER VARCHAR2(25)货期DESCRIPTION VARCHAR2(240)说明表名：PO.RCV_TRANSACTIONS说明：到货接收TRANSACTION_ID NUMBER接收交易IDCREATION_DATE DATE创建时间CREATED_BY NUMBER创建人IDREQUEST_ID NUMBERTRANSACTION_TYPE VARCHAR2(25)交易类型* TRANSACTION_DATE DATE交易日期QUANTITY NUMBER数量UNIT_OF_MEASURE VARCHAR2(25)单位SHIPMENT_HEADER_ID NUMBER发运头IDSHIPMENT_LINE_ID NUMBER发运行IDINV_TRANSACTION_ID NUMBER INV模块交易登记ID SOURCE_DOCUMENT_CODE VARCHAR2(25)来源文档DESTINATION_TYPE_CODE VARCHAR2(25)目的类型* PRIMARY_QUANTITY NUMBER主单位数量PRIMARY_UNIT_OF_MEASURE VARCHAR2(25)主单位EMPLOYEE_ID NUMBER(9)员工IDPARENT_TRANSACTION_ID NUMBER父交易IDPO_HEADER_ID NUMBER PO头IDPO_RELEASE_ID NUMBERPO_LINE_ID NUMBER PO行IDPO_LINE_LOCATION_ID NUMBER PO定位行IDPO_DISTRIBUTION_ID NUMBER PO发散IDPO_REVISION_NUM NUMBER PO版本REQUISITION_LINE_ID NUMBER PR行IDPO_UNIT_PRICE NUMBER单位CURRENCY_CODE VARCHAR2(15)币种CURRENCY_CONVERSION_TYPE VARCHAR2(30)货币转换类型CURRENCY_CONVERSION_RATE NUMBER汇率CURRENCY_CONVERSION_DATE DATE汇率日期DELIVER_TO_PERSON_ID NUMBER(9)投递给员工ID DELIVER_TO_LOCATION_ID NUMBER投递地点IDVENDOR_ID NUMBER供应商IDVENDOR_SITE_ID NUMBER供应商地址IDORGANIZATION_ID NUMBER组织代码SUBINVENTORY VARCHAR2(10)子库LOCATION_ID NUMBER库位ID（与inv.mtl_item_locations关联）RMA_REFERENCE VARCHAR2(30)RMA参考COMMENTS VARCHAR2(240)备注INVOICE_ID NUMBER发票IDINVOICE_STATUS_CODE VARCHAR2(25)发票状态说明：TRANSACTION_TYPE 与 DESTINATION_TYPE_CODE 取值TRANSACTION_TYPEACCEPT接受检验合格CORRECT校正录入错误,信息调整DELIVER交货正式入库RECEIVE接收收货REJECT拒绝接收后未作入库或退货处理RETURN TO RECEIVING 返回至接收从库房返回到暂存库RETURN TO VENDOR返回至供应商从暂存库/库房/车间退货到供应商TRANSFER转帐香港暂存库转公司暂存库DESTINATION_TYPE_CODEEXPENSE费用INVENTORY库房RECEIVING暂存SHOP FLOOR车间表名:PO.PO_VENDORS说明:供应商表VENDOR_ID NUMBER供应商IDVENDOR_NAME VARCHAR2(80)供应商名称SEGMENT1 VARCHAR2(30)供应商编号CREATION_DATE DATECREATED_BY NUMBEREMPLOYEE_ID NUMBER员工供应商的员工IDVENDOR_TYPE_LOOKUP_CODE VARCHAR2(25)供应商类型代码CUSTOMER_NUM VARCHAR2(25)MIN_ORDER_AMOUNT NUMBERSHIP_TO_LOCATION_ID NUMBER发运地点IDBILL_TO_LOCATION_ID NUMBER开标地点IDSHIP_VIA_LOOKUP_CODE VARCHAR2(25)发运方法FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25)运费条款FOB_LOOKUP_CODE VARCHAR2(25)FOBTERMS_ID NUMBER付款条款IDSET_OF_BOOKS_ID NUMBER财务帐簿CREDIT_STATUS_LOOKUP_CODE VARCHAR2(25)CREDIT_LIMIT NUMBERALWAYS_TAKE_DISC_FLAG VARCHAR2(1)PAY_DATE_BASIS_LOOKUP_CODE VARCHAR2(25)PAY_GROUP_LOOKUP_CODE VARCHAR2(25)PAYMENT_PRIORITY NUMBERINVOICE_CURRENCY_CODE VARCHAR2(15)发票币种PAYMENT_CURRENCY_CODE VARCHAR2(15)INVOICE_AMOUNT_LIMIT NUMBEREXCHANGE_DATE_LOOKUP_CODE VARCHAR2(25)HOLD_ALL_PAYMENTS_FLAG VARCHAR2(1)HOLD_FUTURE_PAYMENTS_FLAG VARCHAR2(1)HOLD_REASON VARCHAR2(240)DISTRIBUTION_SET_ID NUMBERACCTS_PAY_CODE_COMBINATION_ID NUMBERDISC_LOST_CODE_COMBINATION_ID NUMBERDISC_TAKEN_CODE_COMBINATION_ID NUMBER EXPENSE_CODE_COMBINATION_ID NUMBER PREPAY_CODE_COMBINATION_ID NUMBERNUM_1099 VARCHAR2(30)TYPE_1099 VARCHAR2(10)WITHHOLDING_STATUS_LOOKUP_CODE VARCHAR2(25) WITHHOLDING_START_DATE DATE ORGANIZATION_TYPE_LOOKUP_CODE VARCHAR2(25) VAT_CODE VARCHAR2(15)START_DATE_ACTIVE DATEEND_DATE_ACTIVE DATEMINORITY_GROUP_LOOKUP_CODE VARCHAR2(25) PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(25) BANK_ACCOUNT_NAME VARCHAR2(80)BANK_ACCOUNT_NUM VARCHAR2(30)BANK_NUM VARCHAR2(25)BANK_ACCOUNT_TYPE VARCHAR2(25)WOMEN_OWNED_FLAG VARCHAR2(1)SMALL_BUSINESS_FLAG VARCHAR2(1)STANDARD_INDUSTRY_CLASS VARCHAR2(25)HOLD_FLAG VARCHAR2(1)PURCHASING_HOLD_REASON VARCHAR2(240) HOLD_BY NUMBER(9)HOLD_DATE DATETERMS_DATE_BASIS VARCHAR2(25)PRICE_TOLERANCE NUMBERINSPECTION_REQUIRED_FLAG VARCHAR2(1) RECEIPT_REQUIRED_FLAG VARCHAR2(1)QTY_RCV_TOLERANCE NUMBERQTY_RCV_EXCEPTION_CODE VARCHAR2(25)ENFORCE_SHIP_TO_LOCATION_CODE VARCHAR2(25)DAYS_EARLY_RECEIPT_ALLOWED NUMBERDAYS_LATE_RECEIPT_ALLOWED NUMBERRECEIPT_DAYS_EXCEPTION_CODE VARCHAR2(25)RECEIVING_ROUTING_ID NUMBERALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1)ALLOW_UNORDERED_RECEIPTS_FLAG VARCHAR2(1)HOLD_UNMATCHED_INVOICES_FLAG VARCHAR2(1)EXCLUSIVE_PAYMENT_FLAG VARCHAR2(1)TAX_VERIFICATION_DATE DATENAME_CONTROL VARCHAR2(4)STATE_REPORTABLE_FLAG VARCHAR2(1)FEDERAL_REPORTABLE_FLAG VARCHAR2(1)BANK_NUMBER VARCHAR2(30)EDI_TRANSACTION_HANDLING VARCHAR2(25)EDI_PAYMENT_METHOD VARCHAR2(25)EDI_PAYMENT_FORMAT VARCHAR2(25)EDI_REMITTANCE_METHOD VARCHAR2(25)EDI_REMITTANCE_INSTRUCTION VARCHAR2(256)BANK_CHARGE_BEARER VARCHAR2(1)BANK_BRANCH_TYPE VARCHAR2(25)ATTRIBUTE1关联公司ATTRIBUTE2制造商或代理商ATTRIBUTE3制造或OEM ATTRIBUTE4结算币种ATTRIBUTE5专家团ATTRIBUTE6供应商等级ATTRIBUTE7DUNS号ATTRIBUTE8供应商网址ATTRIBUTE9供应商英文地址表名:PO.PO_VENDOR_SITES_ALL说明:供应商地点表VENDOR_SITE_ID NUMBER供应商地点IDVENDOR_ID NUMBER供应商IDVENDOR_SITE_CODE VARCHAR2地点代码VENDOR_SITE_CODE_ALT VARCHAR2地点替代名称PURCHASING_SITE_FLAG VARCHAR2是否采购地点RFQ_ONLY_SITE_FLAG VARCHAR2仅RFQPAY_SITE_FLAG VARCHAR2是否付款地点ADDRESS_LINE1VARCHAR2地址行1ADDRESS_LINES_ALT VARCHAR2Alternate address line for Kana Value ADDRESS_LINE2VARCHAR2地址行2ADDRESS_LINE3VARCHAR2地址行3CITY VARCHAR2CitySTATE VARCHAR2State name or abbreviationZIP VARCHAR2Postal codePROVINCE VARCHAR2ProvinceCOUNTRY VARCHAR2Country nameAREA_CODE VARCHAR2Area codePHONE VARCHAR2Phone numberSHIP_TO_LOCATION_ID NUMBER Default ship-to location uniqueidentifierBILL_TO_LOCATION_ID NUMBER Ship-to location unique identifier SHIP_VIA_LOOKUP_CODE VARCHAR2Default carrier typeFREIGHT_TERMS_LOOKUP_CODE VARCHAR2Default freight terms typeFOB_LOOKUP_CODE VARCHAR2Default free-on-board type INACTIVE_DATE DATE Inactive date for recordFAX VARCHAR2Customer site facsimile numberFAX_AREA_CODE VARCHAR2Customer siteTELEX VARCHAR2T elex numberPAYMENT_METHOD_LOOKUP_CODE VARCHAR2Default payment method typeBANK_ACCOUNT_NAME VARCHAR2Bank account nameBANK_ACCOUNT_NUM VARCHAR2Bank account numberBANK_NUM VARCHAR2Bank numberBANK_ACCOUNT_TYPE VARCHAR2Bank account typeTERMS_DATE_BASIS VARCHAR2Type of invoice payment schedule basis VAT_CODE VARCHAR2税码PAY_GROUP_LOOKUP_CODE VARCHAR2付款组PAYMENT_PRIORITY NUMBER付款优先级TERMS_ID NUMBER付款条款IDINVOICE_CURRENCY_CODE VARCHAR2发票币种PAYMENT_CURRENCY_CODE VARCHAR2付款币种ATTRIBUTE14VARCHAR2直实海外供应商地点IDATTRIBUTE15VARCHAR2海外供应商编号VAT_REGISTRATION_NUM VARCHAR2税务登计号ORG_ID NUMBER地点所属的经营单位CHECK_DIGITS VARCHAR2Check digits for Accounts Payable BANK_NUMBER VARCHAR2Bank number for Accounts Payable ADDRESS_LINE4VARCHAR2地址行4COUNTY VARCHAR2Supplier site countyADDRESS_STYLE VARCHAR2Style of addressMATCH_OPTION VARCHAR2匹配选项EMAIL_ADDRESS VARCHAR2邮件地址PRIMARY_PAY_SITE_FLAG VARCHAR2主要付款地点表名:PO.PO_VENDOR_CONTACTS说明:供应商联系人表VENDOR_CONTACT_ID NUMBER联系人IDVENDOR_SITE_ID NUMBER供应商地点IDINACTIVE_DATE DATE失效日期FIRST_NAME VARCHAR2Contact first nameMIDDLE_NAME VARCHAR2Contact middle nameLAST_NAME VARCHAR2Contact last namePREFIX VARCHAR2联系人前缀TITLE VARCHAR2TitleMAIL_STOP VARCHAR2邮件AREA_CODE VARCHAR2区号PHONE VARCHAR2联系人电话FIRST_NAME_ALT VARCHAR2Alternate supplier contact's first name LAST_NAME_ALT VARCHAR2Alternate supplier contact's last name DEPARTMENT VARCHAR2联系人部门CONTACT_NAME_ALT VARCHAR2联系人英文名称关联）TION_ID关联）。
ORACLE（系统表emp）基本与深入学习
ORACLE（系统表emp）基本与深⼊学习(⼀)、⾸先我们先创建emp表（系统有的可以跳过往下看）没有直接复制运⾏即可。
create table DEPT(deptno NUMBER(2) not null,dname VARCHAR2(14),loc VARCHAR2(13));alter table DEPTadd constraint PK_DEPT primary key (DEPTNO);create table EMP(empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(9),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2));insert into DEPT (deptno, dname, loc)values (10, 'ACCOUNTING', 'NEW YORK');insert into DEPT (deptno, dname, loc)values (20, 'RESEARCH', 'DALLAS');insert into DEPT (deptno, dname, loc)values (30, 'SALES', 'CHICAGO');insert into DEPT (deptno, dname, loc)values (40, 'OPERATIONS', 'BOSTON');commit;insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);commit;(⼆)题⽬学习与实践。