Oracle高级复制Step by Step

oracle中imp命令详解Oracle的导入实用程序(Importutility)允许从数据库提取数据，并且将数据写入操作系统文件。

imp使用的基本格式：imp [username[/password[@service]]]，以下例举imp常用用法。

1. 获取帮助imp help=y2. 导入一个完整数据库imp system/manager file=d:\daochu.dmp log=d:\daochu.log full=y ignore=y3. 导入一个或一组指定用户所属的全部表、索引和其他对象imp system/manager file=seapark log=seapark fromuser=seapark impsystem/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)4. 将一个用户所属的数据导入另一个用户imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1,amy1)5. 导入一个表imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)6. 从多个文件导入imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4)log=paycheck, filesize=1G full=y7. 使用参数文件imp system/manager parfile=bible_tables.parbible_tables.par参数文件：#Import the sample tables used for the Oracle8i Database Administrator's Bible. fromuser=seapark touser=seapark_copy file=seapark log=seapark_import8. 增量导入imp system./manager inctype= RECTORE FULL=Y FILE=A Oracle imp/expC:Documentsand Settingsadministrator>exp help=yExport: Release 9.2.0.1.0 - Production on 星期三 7月 28 17:04:43 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 通过输入 EXP命令和用户名/口令，您可以后接用户名/口令的命令:例程: EXPSCOTT/TIGER或者，您也可以通过输入跟有各种参数的 EXP命令来控制“导出”按照不同参数。

客户的数据库采用expdp命令导出的，这是Oracle10g以后采用的新的导出方式，比exp 执行效率上有所提高。

下面说明如何做导入导出：1、环境变量设置(在Sqlplus中执行)create or replace directory <dir_name> as ‘c:\tmpdir’;注：c:\tmpdir也可以是另外的随意目录，用单引号括起来2、导出ARADMIN库expdp <username>/<password>@<SID> directory=<dir_name> dumpfile=xxxxxx.datlogfile=xxxx.log schemas=ARADMIN;3、如果需要备份（通常不需要）CMDB WebService则导出AtriumAdmin库expdp <username>/<password>@<SID> directory=<dir_name> dumpfile=xxxxxx.datlogfile=xxxx.log schemas=ARADMIN;4、导出成功后，把dat和log文件都拷贝到目标数据库服务器上，记住两个文件的放置路径5、目标库导入前操作create or replace directory <dir_name_1> as ‘c:\tmpdir’;注：此处的c:\tmpdir替换成两个文件的存放路径Sqlplus登录目标库(一定要用管理员账号)；sqlplus <username>/<password>@<SID> as sysdba;核对连接的库名：show parameter name; 查看连接的是哪个SID;select username from dba_users order by created;查看所有表空间命令的最后两行应该有ARADMIN和ATRIUMADMIN表空间名；删除目标库里面原来的表空间（删之前停AR服务）：drop user ARADMIN cascade;未停服务时报错停服务后成功创建新的用户并赋权限：CREATE USER "ARADMIN" IDENTIFIED BY ARAdmin default tablespace ARSYSTEMtemporary tablespace TEMP quota unlimited on ARSYSTEM;Grant alter session,create cluster,create database link,create sequence,create session, create synonym,create table,create view,create procedure,create trigger,query rewrite to aradmin;6、导入库命令Impdp <username>/<password>@<SID> directory=<dir_name_1> dumpfile=xxxx.dat logfile=xxxx.log schemas=ARADMIN。

在Oracle 10g下，我们可以使用RMAN duplicate 命令创建一个具有不同DBID 的复制库。

到了Oracle 11gR2，RMAN 的duplicate 有2种方法实现：1. Active database duplication2. Backup-based duplicationActive database duplication 通过网络，直接copy target 库到auxiliary 库，然后创建复制库。

这种方法就不需要先用RMAN 备份数据库，然后将备份文件发送到auxiliary端。

这个功能的作用是非常大的。

尤其是对T级别的库。

因为对这样的库进行备份，然后将备份集发送到备库，在进行duplicate 的代价是非常大的。

一备份要占用时间，二要占用备份空间，三在网络传送的时候，还需要占用带宽和时间。

所以Active database duplicate 很好的解决了以上的问题。

它对大库的迁移非常有用。

如果是从RAC duplicate 到单实例，操作是一样的。

如果是从单实例duplicate 到RAC。

那么先duplicate 到单实例。

然后将单实例转换成RAC。

Oracle 10g 下Rman duplicate 的例子参考：RMAN 异机复制数据库下面我们看一下11gR2下，Active Database Duplicate的步骤如下：1. 创建Auxiliary 库的Initialization Parameter：如果使用spfile，那么在pfile文件里只需要设置一个DB_NAME参数，其他参数会在duplicate 命令中自己设置。

如果使用pfile，那么需要设置如下参数：DB_NAMECONTROL_FILESDB_BLOCK_SIZEDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTDB_RECOVERY_FILE_DEST2. 在Auxiliary库创建Password File 文件对于Backup-based duplication，Password File 不是必须的，但是对于Active Database Duplication，Password File是必须的。

/goto/oraclemidsizeSUMMER 2014RE-IMAGINING DATA MANAGEMENTComplete, integrated solutions for managing growing data volumes and keeping your business running smoothly as it grows.PAGE 2THE SUPPORTED CLOUDLow-risk cloud computing.PAGE 2THE ALWAYS ON BUSINESSStop growth slowing your business down.PAGE 3AUTOMATICALLY BETTER MARKETINGEnsure a better return with less effort.PAGE 4DATABASE AS A SERVICE IN ACTIONSmall business performance transformed.PAGE 5ORACLE MAGAZINE FOR MIDSIZE COMPANIESRe-imagining data managementHow much control do you have over the data you’re business is generating? How much time, money and resources are you using to manage it? Could you be extracting more value from it with less effort if you had the right tools?Having great answers to questions like these could make the difference between your business having a good year or a great one. It could also put you one step ahead of your competitors. The question is how you implement the solutions that put you in that enviable position.Putting your trust in the cloudManaging data and consuming applications as a service requires fewer resources and less investment than owning and managing your own servers within your own building. That’s why it’s so perfect for midsize businesses. And by choosing the rightprovider, you don’t have to worry about the availability or security issues that relying on unsupported open-source solutions can create. Oracle Database 12c was built to be the foundation of an efficient, effective and secure cloud computing environment for fast-growing businesses like yours. Y ou can choose to use it as a service via the Oracle Cloud, alongside powerful SaaS tools, or deploy it in a private cloud environment to retain more control over your most sensitive data.Either way, you get the scalable and high-performance and highly available infrastructure your business needs to continue its successful growth trajectory far into the future.This edition of Oracle ONE Magazine shows you how to use Oracle’s complete range of integrated hardware, software and solutions to manage your data optimally, and tap the power of cloud computing as your business grows. We hope you find it useful.Even clouds need supportFor all its benefits, it still pays to take a cautious approach when building a cloud-based database environment. So you need to ask the question: do you really want to risk business continuity by using a solution that involves reprogramming your apps, and which relies too heavily on unsupported open source technology?Oracle Database 12c , based on proven enterprise-class technology, plugs your business into the cloud with maximum support and minimum risk – all without making any changes to your existing applications.»Register for an Oracle Database 12c webcast now: /goto/technology/meaORACLE ONE MAGAZINE 2Black KeylineREADER01LASER%RELEASED3/242014Oracle and CetelemSpanish Financial Services firm Cetelem, a local subsidiary of the BNP Paribas personal finance division, needed to drive business agility and new revenue by reducing time to market for new products and services.The company used Oracle WebLogic Server, as part of anDealis Fund Operations GmbH is a German joint venture between Allianz Global Investors and DekaBank. Its 330 employees determine the daily price of administered funds based on the The company needed to improve back-office systemperformance to increase the reliability and efficiency of transaction management, while ensuring business continuity without increasing T4-1 and T4-4Oracle and 3 Step IT Group Based in Finland, 3 Step IT Group Oy provides global IT equipment leasing services as well as online asset management and equipment replacement services to around 3,000 customers in Scandinavia, the UK, and across Asia. It needed to guarantee high service availability for its rapidly growing customer base.3 Step IT Group implemented a complete Oracle solution featuring Oracle Database Appliance, Oracle Active Data Guard and Oracle Real Application Clusters. This enabled it to eliminate unplanned maintenance breaks and downtime, and replicate data live between the production site and the standby testing site.»Read the case study for more detail on the benefits now: /goto/technology/mea Single appliances, multiple strengthsThese days, you don’t needa raft of hardware to handle different data management and storage tasks.A single Oracle Database Appliance, combined with a single Oracle Sun ZS3 Storage Appliance can protect your data, enable fast and easy development and testing, rapidly expand storage capacity, tier your data storage intelligently, and lower storage costs.»Want to know if it’s right for you? Take our Oracle Database Appliance readiness test now: /goto/it-infrastructure/meaForget format, justmanage your dataWouldn’t it be nice if all your datawas in the same format – just thinkhow easy it would be to manage!Unfortunately, even Oracle can’tmake that dream a reality. However,with Oracle Database 12c, youcan manage and analyse all yourstructured and unstructured datamore easily.That’s important, because unstructureddata, such as that often producedvia social media and mobile devices,is increasing in volume within manyorganisations. Not only that, this kindof data can often provide some of thefreshest, most useful insights aboutwhat’s going on within a business or acustomer segment right now.Oracle Database 12c simplifies themanagement of unstructured data bysupporting specialized data types for manycommon data formats, making it as easyto interact with as standard relationaldata. And because it’s stored alongsideall your other data, you can use all thesame administrative, monitoring andmanagement features to handle it.»Our white paper reveals the value in moredetail. Read it now:/goto/technology/mea4JOIN US ON THE COUCH。

oracle 多段with as用法-回复什么是Oracle多段WITH AS语句？Oracle多段WITH AS语句是一种被称为“公共表表达式（CTE）”的SQL 语法，它允许我们定义一个或多个临时命名结果集，以供后续查询使用。

这样，我们可以在一个SQL查询中多次引用这个结果集，从而简化复杂查询的编写过程，提高查询的可读性和可维护性。

为什么需要使用Oracle多段WITH AS语句？在Oracle数据库中，为了获取所需数据，我们常常需要进行多个嵌套的子查询。

这样的查询结构不仅使得SQL语句繁琐复杂，也降低了查询的效率。

使用Oracle多段WITH AS语句，我们可以将这些子查询转换为临时命名结果集，使得查询语句更加清晰简洁，同时也提高了查询的性能。

使用Oracle多段WITH AS语句的步骤是怎么样的？Step 1: 定义WITH AS子句及其结果集首先，在你的SQL查询语句之前，使用WITH AS子句来定义一个或多个临时命名结果集。

每个结果集由一个唯一的名称和一个SQL查询语句组成。

可以在结果集中使用列别名来为列命名。

例如，我们想要计算订单表中每个用户的总订单金额，可以使用以下WITH AS语句来定义结果集：WITH order_totals AS (SELECT customer_id, SUM(order_amount) AS total_amount FROM ordersGROUP BY customer_id)Step 2: 引用WITH AS结果集进行查询一旦我们定义了临时命名结果集，就可以在后续的查询中引用它们。

可以像引用表一样引用结果集，并在查询中使用它们来筛选、排序或汇总数据。

例如，我们想要获取订单总金额大于1000的所有用户的详细信息，可以使用以下查询语句：SELECT *FROM customersWHERE customer_id IN (SELECT customer_id FROM order_totals WHERE total_amount > 1000)Step 3: 完成查询，并使用WITH AS语句的结果集最后，在你的查询语句结束后，你可以继续使用WITH AS语句定义的结果集。

Setup of Advanced Pricing Qualifiers and Modifiers at _____________. Steps1.Login and select the Application Administrator Responsibility2.Create the value sets.3.Login and select the Pricing Manager Responsibility4.Setup the Pricing Attributes, and assign the value sets to the declared PricingAttributes5.Review the pl/sql program (mwac_qp_qualifications) to find the parameters topass, and what to receive.6.Setup the attributes and modifiers for the program call within Order Management7.Setup the Pricing Modifier’s qualifications as developed in Step 2 above.8.Change the responsibility to Order Management.9.Test the results using the Pricing Viewer.Login and select the Application Administrator ResponsibilityCreate the value sets using the already created value sets from the DTB team. For a naming convention the DTB team used ‘MFSG_BRAND_VS’ and create the advanced pricing (QP) value set named ‘MFSG_BRAND_QP_VS’.Below is the base value set: MFSG_BRAND_VSCreate the new Value Set as a type of ‘Table’ titled MFSG_BRAND_QP_VS. Once completed press the Edit Information button.Select the Pricing Manager Responsibility.Setup the Qualification Attributes by selecting the Context and Attributes menu option.Enter the name of the qualifier and pricing transacton entity. In the next block, enter the name of the field and attribute. As shown below, BRAND is assigned toQualifier_Attribute34 (you are allowed 31 to 100) for custom qualification attributes.Assign the value set previous done for QP to this attribute:Save your results. Also check to make sure you have the correct value set by pressing the ‘Value Set’ button.Next setup the Attributes call to the MWAC_QP_QUALIFICATIONS pl/sql program.Select the PTE entry (order fullfillment) and qualifier context then Order. Press the Contents button to verify you have the correct attribute:The screen below provides the validation of the BRAND qualifier. Press the value sets button to validate the correct Value Set was chosen. If not then update this screen. Once completed press the save icon and go back one screen.Now press the Link Attributes buttonSearch and select the Qualifier Attribute you are looking for. Select the level at which this qualifier is used for (sales order header, line level, or both) – see high lite below. Then press the Attribute Mapping button.Select the Request Type that is being updated – in this case select the 3rd row for Order Management.Validate you have the correct qualifier attributes, and press the Attribute Mapping button. The screen below should be displayed. Next press the Oracle Pricing line for Order Management Order, and update the calling segments (by line, header, or both depending upon where the call is made (SO line, SO header, or both). Update the User Source Type to PL/SQL API, and set the User Value String to call the custom program. In the example below only the sales order line is used to pass information. The value in the blue highlite is‘MWAC_QP_QUALIFICATIONS.GET_QP_BRAND(OE_ORDER_PUB.G_LINE.LIN E_ID)’ which means the pl/sql package mwac_qp_qualifications is being called to get the Brand Value get_qp_brand by using the line_id in the program headerow_order_pub.g_line.line_id.Save your changes and exit until you get to the Main Menu screen.Note: The passing value (ie layout) of the sales order line id can be found in Oracle’s Order Management User Guide.Select the Modifier setup on the menuQuery the modifer that will be using the qualifier already developed.Press the Qualification list at either the modifer’s header or line level (header is the top part of the form “List Qualifier” and line is the lower portion of the form “Line Qualifier”). In the example below the List Qualifier was pressed and the qualifier was entered as the second condition.Select the epsilon (…) and a pop up of your previously defined value set appears (in this case ‘MFSG_BRAND_QP_VS’. In this example MII was chosen as the BRAND.Update the Start date and press the OK button. This will save your results. From here exit all the way to the main menu, select the Order Management responsibility, and test your Price modifier.Select the Order Management responsibility and start testing the Pricing Modifier results.Press the Sales Orders menu optionEnter the sales order.Enter the sales line and press the save button (this should automatically price the sales line). If not press the Actions button and press the Price Line command (see next screen shot).Press ok to price the line.Validate the updates by viewing the price adjustments (press the Action and then View Adjustments command line).The modifiers from TEST REP were used to update the pricing in this order.Another way to view any adjustment is to use the pricing engine’s viewer screen. First turn on the profile option to show the debug on/off status. Turn this on.Next select the view on the Tools menu option.Now you can review the details of the pricing engine. This shows all the qualifiers and modifiers that were used to derive the final price on each line and order.Press which line was changed (in this case sale order line #2 was inserted).Press the Attributes button. Then the screen below shows with all the qualification attributes and their values.By pressing the Debug button you can see the actual Qualification Attributes and the resulting values.End of document.You are ready to setup and test your Advanced Pricing Qualifiers and Modifiers.PL/SQL Program ListingCREATE OR REPLACE PACKAGE MWAC_QP_QUALIFICATIONS AUTHID CURRENT_USERAS/*******************************************************************************************************-- **-- (c) Oracle India Private Limited, NAIO **-- **-- Package Name: MWAC_QP_QUALIFICATIONS Author: ArunMalhotra **-- (SPECIFICATION) **--**-- Creation Date: 24-MAR-2005 **-- Version Number: 1.0 Last Modified:24-MAR-2005 **-- **-- **-- Development and Maintenance History: **-- **-- Date Nodified By Current VersionDescription **-- ---------- ----------- ---------------- --------------- **-- 24/03/2005 Arun Malhotra 1.0 Initial VersionCoding **-- **--****************************************************************************************************/FUNCTION Get_QP_SALESREP (p_salesrep_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_ZIPCODE (p_ship_to_site_code_id IN NUMBER)RETURNVARCHAR2;FUNCTION Get_QP_STATE (p_ship_to_site_code_id IN NUMBER)RETURNVARCHAR2;FUNCTION Get_QP_BRAND (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_BRAND_ID (p_order_line_id IN NUMBER)RETURN NUMBER;FUNCTION Get_QP_PRODLINE (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_PRODTYPE (p_order_line_id IN NUMBER)RETURN VARCHAR2;FUNCTION Get_QP_PRODLNGRP (p_order_line_id IN NUMBER)RETURN VARCHAR2;END MWAC_QP_QUALIFICATIONS;/PL/SQL Package BodyCREATE OR REPLACE PACKAGE BODY MWAC_QP_QUALIFICATIONSAS/*******************************************************************************************************-- **-- (c) Oracle India Private Limited, NAIO **-- **-- Package Name: MWAC_QP_QUALIFICATIONS Author: ArunMalhotra **-- (SPECIFICATION) **--**-- Creation Date: 24-MAR-2005 **-- Version Number: 1.0 Last Modified:24-MAR-2005 **-- **-- **-- Development and Maintenance History: **-- **-- Date Nodified By Current VersionDescription **-- ---------- ----------- ---------------- --------------- **-- 24/03/2005 Arun Malhotra 1.0 Initial VersionCoding **-- **--****************************************************************************************************/--FUNCTION Get_QP_SALESREP (p_salesrep_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_salesrepname (l_salesrep_id NUMBER)ISSELECT nameFROM jtf_rs_salesreps jrsWHERE jrs.salesrep_id = l_salesrep_id;lc_name jtf_rs_%TYPE;BEGINlc_name :='';OPEN get_salesrepname (p_salesrep_id);FETCH get_salesrepname INTO lc_name;IF(get_salesrepname%NOTFOUND)THENRETURN lc_name;END IF;CLOSE get_salesrepname;RETURN lc_name;END Get_QP_SALESREP;FUNCTION Get_QP_ZIPCODE (p_ship_to_site_code_id IN NUMBER) RETURN VARCHAR2ISCURSOR get_postalcode (l_ship_to_site_code_id NUMBER)IS SELECT loc.postal_codeFROM HZ_CUST_SITE_USES_ALL SITE,HZ_PARTY_SITES PARTY_SITE,HZ_LOCATIONS LOC,HZ_CUST_ACCT_SITES_ALL ACCT_SITEWHERE SITE.SITE_USE_CODE ='SHIP_TO'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_IDAND PARTY_SITE.LOCATION_ID = LOC.LOCATION_IDAND SITE.SITE_USE_ID = l_ship_to_site_code_id;lc_postalcode HZ_LOCATIONS.POSTAL_CODE%TYPE;BEGINlc_postalcode :='';OPEN get_postalcode(p_ship_to_site_code_id);FETCH get_postalcode INTO lc_postalcode;IF(get_postalcode%NOTFOUND)THENRETURN lc_postalcode;END IF;CLOSE get_postalcode;RETURN lc_postalcode;END Get_QP_ZIPCODE;FUNCTION Get_QP_STATE (p_ship_to_site_code_id IN NUMBER) RETURN VARCHAR2ISCURSOR get_state (l_ship_to_site_code_id NUMBER)IS SELECT loc.stateFROM HZ_CUST_SITE_USES_ALL SITE,HZ_PARTY_SITES PARTY_SITE,HZ_LOCATIONS LOC,HZ_CUST_ACCT_SITES_ALL ACCT_SITEWHERE SITE.SITE_USE_CODE ='SHIP_TO'AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_IDAND PARTY_SITE.LOCATION_ID = LOC.LOCATION_IDAND SITE.SITE_USE_ID = l_ship_to_site_code_id;lc_state HZ_LOCATIONS.STATE%TYPE;BEGINlc_state :='';OPEN get_state(p_ship_to_site_code_id);FETCH get_state INTO lc_state;IF(get_state%NOTFOUND)THENRETURN lc_state;END IF;CLOSE get_state;RETURN lc_state;END Get_QP_STATE;FUNCTION Get_QP_BRAND (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_brand (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment3FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_brand MTL_CATEGORIES_B.SEGMENT3%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_brand :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_brand;END IF;CLOSE get_item_id;OPEN get_brand(ln_item_id,ln_ship_from_org_id);FETCH get_brand INTO lc_brand;IF(get_brand%NOTFOUND)THENRETURN lc_brand;END IF;CLOSE get_brand;RETURN lc_brand;END Get_QP_BRAND;FUNCTION Get_QP_BRAND_ID (p_order_line_id IN NUMBER)RETURN numberISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_brand_id (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT ffv.flex_value_idFROM fnd_flex_values ffv,fnd_flex_value_sets ffvsWHERE ffv.FLEX_VALUE_SET_ID = ffvs.flex_value_set_idAND ffvs.flex_value_set_name ='MFSG_BRAND_VS'AND ffv.flex_value =(SELECT mc.segment3FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID =mic.CATEGORY_SET_IDAND mic.inventory_item_id =l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID);lc_brand MTL_CATEGORIES_B.SEGMENT3%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type;ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;ln_brand_id number;BEGINln_brand_id :=0;OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN ln_brand_id;END IF;CLOSE get_item_id;OPEN get_brand_id(ln_item_id,ln_ship_from_org_id);FETCH get_brand_id INTO ln_brand_id;IF(get_brand_id%NOTFOUND)THENRETURN NULL;END IF;CLOSE get_brand_id;RETURN ln_brand_id;END Get_QP_BRAND_ID;FUNCTION Get_QP_PRODLINE (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodline (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment4FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodline MTL_CATEGORIES_B.SEGMENT4%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodline :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodline;END IF;CLOSE get_item_id;OPEN get_prodline(ln_item_id,ln_ship_from_org_id);FETCH get_prodline INTO lc_prodline;IF(get_prodline%NOTFOUND)THENRETURN lc_prodline;END IF;CLOSE get_prodline;RETURN lc_prodline;END Get_QP_PRODLINE;FUNCTION Get_QP_PRODTYPE (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodtype (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment5FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodtype MTL_CATEGORIES_B.SEGMENT5%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodtype :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodtype;END IF;CLOSE get_item_id;OPEN get_prodtype(ln_item_id,ln_ship_from_org_id);FETCH get_prodtype INTO lc_prodtype;IF(get_prodtype%NOTFOUND)THENRETURN lc_prodtype;END IF;CLOSE get_prodtype;RETURN lc_prodtype;END Get_QP_PRODTYPE;FUNCTION Get_QP_PRODLNGRP (p_order_line_id IN NUMBER)RETURN VARCHAR2ISCURSOR get_item_id(l_order_line_id NUMBER)ISSELECT inventory_item_id,ship_from_org_idFROM oe_order_lines_allWHERE line_id = l_order_line_id;CURSOR get_prodlngrp (l_inventory_item_id NUMBER,l_organization_id NUMBER)ISSELECT mc.segment6FROM mtl_categories_b mc,mtl_item_categories mic,mtl_category_sets_tl mcsWHERE mcs.CATEGORY_SET_NAME ='Inventory'AND nguage= userenv('LANG')AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_IDAND mic.inventory_item_id = l_inventory_item_idAND anization_id = l_organization_idAND mic.CATEGORY_ID = mc.CATEGORY_ID;lc_prodlngrp MTL_CATEGORIES_B.SEGMENT6%type;ln_item_id OE_ORDER_LINES_ALL.INVENTORY_ITEM_ID%type; ln_ship_from_org_id OE_ORDER_LINES_ALL.ship_from_org_id%type;BEGINlc_prodlngrp :='';OPEN get_item_id(p_order_line_id);FETCH get_item_id INTO ln_item_id , ln_ship_from_org_id;IF(get_item_id%NOTFOUND)THENRETURN lc_prodlngrp;END IF;CLOSE get_item_id;OPEN get_prodlngrp(ln_item_id,ln_ship_from_org_id);FETCH get_prodlngrp INTO lc_prodlngrp;IF(get_prodlngrp%NOTFOUND)THENRETURN lc_prodlngrp;END IF;CLOSE get_prodlngrp;RETURN lc_prodlngrp;END Get_QP_PRODLNGRP;END MWAC_QP_QUALIFICATIONS;/。

Oracle的imp/exp组件是我们常用的工具，它的一个操作原则就是向下兼容。

下面是据此总结的几个使用规则和相关测试：规则1：低版本的exp/imp可以连接到高版本（或同版本）的数据库服务器，但高版本的exp/imp不能连接到低版本的数据库服务器--1.1 使用9i客户端通过imp连接到10g数据库C:\Documents and Settings\yuechaotian>exp userid=hdtest/test@s67tables=(ab01) rows=n file=d:\x.dmpExport: Release 9.2.0.1.0 - Production on 星期三 2月 20 10:09:55 2008Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集注: 将不会导出表数据（行）即将导出指定的表通过常规路径 .... . 正在导出表 AB01在没有警告的情况下成功终止导出。

--1.2 使用10g客户端通过imp连接9i数据库：连接失败（而不是导出失败）C:\Documents and Settings\yuechaotian>exp userid=hbjb_kf_hd/test@s46 owner=hdtest file=d:\x.dmpExport: Release 10.2.0.1.0 - Production on 星期三 2月 20 09:57:22 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.EXP-00056: 遇到 ORACLE 错误 6550ORA-06550: 第 1 行, 第 41 列:PLS-00302: 必须说明 'SET_NO_OUTLINES' 组件ORA-06550: 第 1 行, 第 15 列:PL/SQL: Statement ignoredEXP-00000: 导出终止失败规则2：高版本exp出的dmp文件，低版本无法imp（无法识别dmp文件）--2.1 使用10g客户端exp出10g的数据C:\Documents and Settings\yuechaotian>exp userid=test/test@orclowner=test file=d:\10g.dmpExport: Release 10.2.0.1.0 - Production on 星期三 2月 20 11:16:39 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服务器使用 AL32UTF8 字符集 (可能的字符集转换)即将导出指定的用户...……导出成功终止, 但出现警告。

Oracle多⾏记录合并连接聚合字符串的⼏种⽅法什么是合并多⾏字符串（连接字符串）呢，例如：SQL> desc test;Name Type Nullable Default Comments------- ------------ -------- ------- --------COUNTRY VARCHAR2(20) YCITY VARCHAR2(20) YSQL> select * from test;COUNTRY CITY-------------------- --------------------中国台北中国⾹港中国上海⽇本东京⽇本⼤阪要求得到如下结果集：------- --------------------中国台北,⾹港,上海⽇本东京，⼤阪实际就是对字符实现⼀个聚合功能，我很奇怪为什么Oracle没有提供官⽅的聚合函数来实现它呢：）下⾯就对⼏种经常提及的解决⽅案进⾏分析（有⼀个评测标准最⾼★★★★★）：1.被集合字段范围⼩且固定型灵活性★性能★★★★难度★这种⽅法的原理在于你已经知道CITY字段的值有⼏种，且还不算太多，如果太多这个SQL就会相当的长。

看例⼦：SQL> select t.country,2 MAX(decode(t.city,'台北',t.city||',',NULL)) ||3 MAX(decode(t.city,'⾹港',t.city||',',NULL))||4 MAX(decode(t.city,'上海',t.city||',',NULL))||5 MAX(decode(t.city,'东京',t.city||',',NULL))||6 MAX(decode(t.city,'⼤阪',t.city||',',NULL))7 from test t GROUP BY t.country8 /COUNTRY MAX(DECODE(T.CITY,'台北',T.CIT-------------------- ------------------------------中国台北,⾹港,上海,⽇本东京,⼤阪,⼤家⼀看，估计就明⽩了（如果不明⽩，好好补习MAX DECODE和分组）。