exp&imp storage

最新推荐文章于 2025-08-12 22:09:30 发布

原创最新推荐文章于 2025-08-12 22:09:30 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#statistics #buffer #object #file #sql #function

Oracle 同时被 2 个专栏收录

523 篇文章

订阅专栏

Oracle EBS

416 篇文章

订阅专栏

本文介绍了使用 Oracle 数据库的 EXP 和 IMP 命令进行数据的导出和导入操作的具体步骤，包括不同参数的设置及其对操作的影响，并展示了如何通过 SQL 函数处理数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.exp commands:

exp userid=a/a owner=('A') BUFFER=500000000 FILE=(A-1.DMP,A-2.dmp,A-3.dmp,A-4.dmp,A-5.dmp,A-6.dmp) filesize=100m indexes=y rows=n statistics=none direct=y

A-1.DMP:file size 6k

2.exp commands:

exp userid=a/a owner=('A') BUFFER=500000000 FILE=(A-D-1.DMP,A-D-2.dmp,A-D-3.dmp,A-D-4.dmp,A-D-5.dmp,A-D-6.dmp) filesize=100m indexes=n rows=y statistics=none direct=y

A-D-1.DMP,A-D-2.dmp,A-D-3.dmp,A-D-4.dmp,A-D-5.dmp,A-D-6.dmp:file size 100M

3.imp commands:

imp userid=b/b BUFFER=500000000 FILE=(A-1.DMP) full=y ignore=y feedback=10000 filesize=100m

SQL>conn b/b

SQL>select * from user_objects

SUM(BYTES/1024/1024) SEGMENT_NAME

--------------------------------------------------

576 TEST_IMP
216 TEST_IMP_IND

SQL>select * from text_imp

0 rows selected.

SQL>

test_imp DDL statement:

-- Create table
create table TEST_IMP
(
OWNER          VARCHAR2(30),
OBJECT_NAME    VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID      NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE    VARCHAR2(19),
CREATED        DATE,
LAST_DDL_TIME DATE,
TIMESTAMP      VARCHAR2(19),
STATUS         VARCHAR2(7),
TEMPORARY      VARCHAR2(1),
GENERATED      VARCHAR2(1),
SECONDARY      VARCHAR2(1)
)
tablespace TEST
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 576
    minextents 1
    maxextents unlimited
);
-- Create/Recreate indexes
create index TEST_IMP_IND on TEST_IMP (OBJECT_ID, OBJECT_NAME)
tablespace TEST
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 216M
    minextents 1
    maxextents unlimited
);

----------------------------------------------------------------------------------------------------

exp userid=t1/t1 owner=('T1') BUFFER=500000000 FILE=(A-1.DMP,A-2.dmp,A-3.dmp) filesize=100m indexes=y rows=n statistics=none direct=y

exp userid=t1/t1 owner=('T1') BUFFER=500000000 FILE=(A-D-1.DMP,A-D-2.dmp,A-D-3.dmp) filesize=100m indexes=n rows=y statistics=none direct=y

imp userid=t2/t2 BUFFER=500000000 FILE=(A-1.DMP) fromuser=t1 touser=t2 ignore=y feedback=10000 filesize=100m

imp userid=t2/t2 BUFFER=500000000 FILE=(A-D-1.DMP) full=y ignore=y feedback=10000 filesize=100m

imp userid=t1/t1 BUFFER=500000000 FILE=(A-1.DMP) full=y ignore=y feedback=10000 filesize=100m

imp userid=t1/t1 BUFFER=500000000 FILE=(A-D-1.DMP) full=y ignore=y feedback=10000 filesize=100m

------------------------------------------------------------------------------------------------------

create or replace function get_num(rowselect number) return number is
result number;
begin
select count(*) into result from test1 where object_id<=rowselect;
return(result);
end get_num;

SQL> var u number;
SQL> create or replace function get_num(rowselect number) return number is
2 result number;
3 begin
4 select count(*) into result from test1 where object_id<=rowselect;
5 return(result);
6 end get_num;
7 /

函数已创建。

SQL> exec :u:=get_num(15000);

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> exec :u:=get_num(15000);

PL/SQL 过程已成功完成。

SQL> print u

U
----------
360096

SQL>

---------------------------------------------------------------------------------------------

create or replace function

SQL> create or replace function get_sal(empname varchar2) return varchar2 is
  2    Result varchar2(100);
  3  begin
  4    select a3 into Result from a where a1=empname;
  5    return(Result);
  6  end get_sal;
  7  /

Function created
SQL> var u varchar2(50);
SQL> exec :u:=substr(get_sal('370785001029'),1,10);

PL/SQL procedure successfully completed

此文的引用地址:

GB2312 http://www.1008.tk/trackback.php?id=9&encode=gb2312

UTF-8 http://www.1008.tk/trackback.php?id=9&encode=utf-8

from:http://www.1008.tk/index.php?id=9