Home » RDBMS Server » Backup & Recovery » time taken by import
time taken by import [message #124059] Thu, 16 June 2005 03:04 Go to next message
omnamahsiva2004
Messages: 2
Registered: June 2005
Junior Member


Hi,

How can I get information about time taken by import through import utility for each table.

Best Regards
Arun
Re: time taken by import [message #124126 is a reply to message #124059] Thu, 16 June 2005 09:14 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I need to test this again.
So i may be wrong.
I `beleive` there is no default method available to clock the process( unless you have some very custom auditing process).
ANd it depends on HOW you do the import. Depending on that, the time taken will vary.
It depends on how you do the import.
( total time taken for the whole import, can be easily measured).
<quoting docs>
First, new tables are created. Then, data is imported and indexes are built. Then triggers are imported, integrity constraints are enabled on the new tables, and any bitmap, functional, and/or domain indexes are built.
</end quoting docs>
As above,
table1 is created, data is imported, then table2 is created.
So, the time difference between the last_ddl_time should be your 'time difference'.
But be-aware that if there are constraints involved, the table is altered after the rows are imported( as shown in the second session). so LAST_DDL_TIME will differ!.

scott@9i > !exp scott/tiger owner=scott file=scott.dmp

Export: Release 9.2.0.4.0 - Production on Thu Jun 16 08:10:21 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                   CHAINED_ROWS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP      14336 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

scott@9i > get clean_self
  1  begin
  2  for mag in (select table_name,table_type from cat)
  3  loop
  4      execute immediate ('drop '||mag.Table_type||'  '||mag.table_name);
  5      DBMS_OUTPUT.PUT_LINE ('drop '||mag.Table_type||'  '||mag.table_name);
  6  end loop;
  7* end;
scott@9i > /
drop TABLE  CHAINED_ROWS
drop TABLE  DEPT
drop TABLE  EMP

PL/SQL procedure successfully completed.

scott@9i > !imp scott/tiger fromuser=scott touser=scott file=scott.dmp

Import: Release 9.2.0.4.0 - Production on Thu Jun 16 08:11:19 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . importing table                 "CHAINED_ROWS"
Note: table contains ROWID column, values may be obsolete          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"      14336 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE procedure foo as"
 "var NUMBER;"
 "bEgin"
 "   var :=' ';"
 "end;"
IMP-00015: following statement failed because the object already exists:
 "CREATE function C_textFormula return Char is"
 "vText Varchar2(500);"
 "begin"
 "Select 'This is the test dated on :- ' || sysdate into vText from dual;"
 "return vText;"
 "end;"
Import terminated successfully with warnings.

scott@9i > alter session set nls_date_format='DD-Mon-YYYY HH24:MI:SS';

Session altered.

scott@9i > column object_Name format a20
scott@9i > select object_name,object_type,last_ddl_time from user_objects;

OBJECT_NAME          OBJECT_TYPE        LAST_DDL_TIME
-------------------- ------------------ --------------------
CHAINED_ROWS         TABLE              16-Jun-2005 08:11:19
C_TEXTFORMULA        FUNCTION           16-Jun-2005 08:11:20
DEPT                 TABLE              16-Jun-2005 08:11:20
EMP                  TABLE              16-Jun-2005 08:11:20
FOO                  PROCEDURE          16-Jun-2005 08:11:20

scott@9i > update emp set empno=rownum;

14336 rows updated.

scott@9i > commit;

Commit complete.

scott@9i > alter table emp add constraint pk_1 primary key (empno);

Table altered.

scott@9i > alter table dept add constraint pk_2 primary key (deptno);

Table altered.

scott@9i > alter table emp add constraint  fk_1 foreign key(deptno) referencing  dept(deptno);

Table altered.


scott@9i > !exp scott/tiger owner=scott file=scott.dmp

Export: Release 9.2.0.4.0 - Production on Thu Jun 16 08:55:16 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                   CHAINED_ROWS          0 rows exported
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP      14336 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

scott@9i > @clean_self
drop TABLE  CHAINED_ROWS
begin
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
ORA-06512: at line 4


scott@9i > drop table dept;
drop table dept
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


scott@9i > drop table emp;

Table dropped.

scott@9i > drop table dept;

Table dropped.

scott@9i > !imp scott/tiger fromuser=scott touser=scott file=scott.dmp indexfile=scottindexfile.sql

Import: Release 9.2.0.4.0 - Production on Thu Jun 16 08:57:21 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . skipping table "CHAINED_ROWS"

. . skipping table "DEPT"

. . skipping table "EMP"

Import terminated successfully without warnings.

scott@9i > !cat scottindexfile.sql

REM  CREATE TABLE "SCOTT"."CHAINED_ROWS" ("OWNER_NAME" VARCHAR2(30),
REM  "TABLE_NAME" VARCHAR2(30), "CLUSTER_NAME" VARCHAR2(30),
REM  "PARTITION_NAME" VARCHAR2(30), "SUBPARTITION_NAME" VARCHAR2(30),
REM  "HEAD_ROWID" ROWID, "ANALYZE_TIMESTAMP" DATE) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST
REM  GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  CREATE TABLE "SCOTT"."DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME"
REM  VARCHAR2(14), "LOC" VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1)
REM  TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM  ... 4 rows
CONNECT SCOTT;
CREATE UNIQUE INDEX "SCOTT"."PK_2" ON "DEPT" ("DEPTNO" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST
GROUPS 1) TABLESPACE "USERS" LOGGING ;
REM  ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_2" PRIMARY KEY
REM  ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM  STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM  "USERS" LOGGING ENABLE ;
REM  CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER NOT NULL ENABLE, "ENAME"
REM  VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE,
REM  "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0))
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576
REM  FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM  ... 14336 rows
CREATE UNIQUE INDEX "SCOTT"."PK_1" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS
2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "USERS" LOGGING ;
REM  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_1" PRIMARY KEY ("EMPNO")
REM  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
REM  1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING
REM  ENABLE ;
REM  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_1" FOREIGN KEY
REM  ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ;
REM  ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_1" ;
Previous Topic: Restore Operation requires old Archived Redo- Log
Next Topic: How to Backup and recover
Goto Forum:
  


Current Time: Fri Mar 29 08:54:26 CDT 2024