In
this practice you will use the Export utility. More specifically you will:
1.
Export the “EMP” table and “DEPT” table from the SCOTT schema.
Verify the file was created.
|
D:>exp
scott/tiger file=export.dmp tables=emp,dept Export:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:23:30 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set About to
export specified tables via Conventional Path ... . .
exporting table
EMP
14 rows exported . .
exporting table
DEPT
4 rows exported Export
terminated successfully without warnings. D:> DIR *.dmp Volume
in drive D has no label. Volume
Serial Number is D80D-3B33 Directory
of D:\ 04/11/2002
12:23p
5,120 export.dmp
1 File(s)
5,120 bytes
0 Dir(s) 132,124,672
bytes free D:\> |
2.
Export the entire SCOTT schema and save the output of the operation to a
file. Verify the files were
created.
|
D:>exp
scott/tiger file=scott.dmp owner=scott log=scott.log Export:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:23:30 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set .
exporting pre-schema procedural objects and actions .
exporting foreign function library names for user SCOTT .
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
BONUS
0 rows exported . .
exporting table
DEPT
4 rows exported . .
exporting table
EMP
14 rows exported . .
exporting table
SALGRADE
10 rows exported .
exporting synonyms .
exporting views .
exporting stored procedures . . . .
exporting post-schema procedural objects and actions .
exporting statistics Export
terminated successfully without warnings.
D:> DIR *.dmp Volume
in drive D has no label. Volume
Serial Number is D80D-3B33 Directory
of D:\ 04/11/2002
12:23p
5,120 export.dmp 04/11/2002
12:29p
6,144 scott.dmp
1 File(s)
11,264 bytes
0 Dir(s) 132,124,672
bytes free D:\> DIR *.log Volume
in drive D has no label. Volume
Serial Number is D80D-3B33 Directory
of D:\ 04/11/2002
12:29p
1,024 scott.log
1 File(s)
1,024 bytes
0 Dir(s) 132,124,672
bytes free D:> |
3.
Export the full database, save the output, and use the Direct Path
feature. Verify the files were
created.
|
D:>exp
system/manager file=full.dmp direct=y log=full.log Export:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:23:30 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set About to
export the entire database ... .
exporting tablespace definitions .
exporting profiles .
exporting user definitions .
exporting roles . . . . about to
export OUTLN's tables via Direct Path ... . .
exporting table
OL$
0 rows exported . .
exporting table
OL$HINTS
0 rows exported . about to
export DBSNMP's tables via Direct Path ... . about to
export U101's tables via Direct Path ... . . . .
exporting user history table .
exporting default and system auditing options .
exporting statistics Export
terminated successfully without warnings. D:> DIR *.dmp Volume
in drive D has no label. Volume
Serial Number is D80D-3B33 Directory
of D:\ 04/11/2002
12:23p
5,120 export.dmp 04/11/2002
12:29p
6,144 scott.dmp 04/11/2002
12:35p
14,792,704 full.dmp
1 File(s)
14,803,968 bytes
0 Dir(s) 132,124,672
bytes free D:\> DIR *.log Volume
in drive D has no label. Volume
Serial Number is D80D-3B33 Directory
of D:\ 04/11/2002
12:29p
1,024 scott.log 04/11/2002
12:35p
46,825 full.log
1 File(s)
47,849 bytes
0 Dir(s) 132,124,672
bytes free D:> |
You
have completed the practice.
In
this practice you will use the Import utility. More specifically you will:
1.
Import the “EMP” table and “DEPT” table into the SCOTT schema.
Verify the tables were created.
|
D:>
imp scott/tiger file=export.dmp tables=emp,dept Import:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:50:17 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
file created by EXPORT:V08.01.07 via conventional path import
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set .
importing SCOTT's objects into SCOTT . .
importing table
"EMP"
14 rows imported . .
importing table
"DEPT" 4
rows imported About to
enable constraints... Import
terminated successfully without warnings. D:>
sqlplus scott/tiger SQL>
select object_name,object_type from user_objects; OBJECT_NAME
OBJECT_TYPE --------------------
------------------ DEPT
TABLE EMP
TABLE PK_DEPT
INDEX PK_EMP
INDEX |
2.
Import the entire SCOTT schema into the database.
Verify the new objects were created.
Hint: Don’t forget to use the IGNORE option to ignore the
creation error on the EMP and DEPT tables.
|
D:>imp
scott/tiger file=scott.dmp full=y ignore=y Import:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:50:17 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
file created by EXPORT:V08.01.07 via conventional path import
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set .
importing SCOTT's objects into SCOTT . .
importing table
"BONUS"
0 rows imported . .
importing table
"DEPT" IMP-00019:
row rejected due to ORACLE error 1 IMP-00003:
ORACLE error 1 encountered ORA-00001:
unique constraint (SCOTT.PK_DEPT) violated Column 1
10 . . . . .
importing table
"SALGRADE"
10 rows imported About to
enable constraints... Import
terminated successfully with warnings.
Notice the import
was successful, however there was warnings because of the constraint
violations from the exisiting tables.
The import continued because the Ignore option was used. D:>
sqlplus scott/tiger SQL>
select object_name,object_type from user_objects; OBJECT_NAME
OBJECT_TYPE --------------------
------------------ BONUS
TABLE DEPT
TABLE EMP
TABLE PK_DEPT
INDEX PK_EMP
INDEX SALGRADE
TABLE |
3.
Import the entire database. Hint: Don’t forget to use the IGNORE
option if any objects already exist
|
D:>imp
system/manager file=full.dmp full=y ignore=y Import:
Release 8.1.7.0.0 - Production on Thu Apr 11 12:50:17 2002 (c)
Copyright 2000 Oracle Corporation.
All rights reserved. Connected
to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the
Partitioning option JServer
Release 8.1.7.0.0 - Production Export
file created by EXPORT:V08.01.07 via direct path import
done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set .
importing U101's objects into U101 .
importing U102's objects into U102 . . Import
terminated successfully with warnings. |
You have completed the practice
In
this practice you practice steps to minimize downtime. More specifically you
will:
1.
Shutdown the database and delete a datafile from the DATA01 tablespace.
|
D:\>
sqlplus “/ as sysdba” SQL>
shutdown immediate Database
closed. Database
dismounted. ORACLE
instance shut down. SQL>!del
d:\oracle\oradata\orcl\data01.dbf
|
2.
Startup the database, offline the missing datafile, and open the
database.
|
SQL>
startup ORACLE
instance started. Total
System Global Area 93089624
bytes Fixed
Size
282456 bytes Variable
Size
58720256 bytes Database
Buffers
33554432 bytes Redo
Buffers
532480 bytes Database
mounted. ORA-01157:
cannot identify/lock data file 10 - see DBWR trace file ORA-01110:
data file 10: 'D:\ORACLE\ORADATA\ORCL\DATA01.DBF' SQL>
alter database datafile 2
'D:\ORACLE\ORADATA\ORCL\DATA01.DBF'
offline; Database
altered. SQL>
alter database open; Database
altered. |
3.
Restore the datafile from a backup, perform recovery, and online the
tablespace.
|
SQL>
!copy \backup\data01.dbf d:\oracle\oradata\orcl\data01.dbf
1 file(s) copied. SQL>
recover datafile 'D:\ORACLE\ORADATA\ORCL\DATA01.DBF'; Media
Recovery Completed. SQL>
alter tablespace DATA01 online; Tablespace
altered. |
You
have completed the practice.
ã
Oracle Corporation, 2002