Oracle Database Error:关于EXP导出时的ORA-01555的错误模拟与排除(不完整版本)
在开始本文档之前,你需要有一套位于Linux系统之上的Oracle Database系统。
在本环境中,它是位于RHEL5.8 64bit上的Oracle Database 11g单实例(Single Instance)架构。
在正式开始EXP之前,需要准备环境,以便作为EXP导出的对象。
一、准备数据:
首先,创建一个独立的表空间:ADAMHUAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
[root@rhel5u10 ~]# su - ora11g [ora11g@rhel5u10 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 02:03:23 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> set linesize 300 SQL> select 2 b.tablespace_name "Tablespace Name",b.datafilelist "Datafile List",b.totalmb "Total MB",(b.totalmb - a.freemb) "Used MB",a.freemb "Free MB", 3 Round((1- a.freemb/b.totalmb)*100,2)||' %' "Usage Percent" 4 from 5 (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a, 6 (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b 7 where 8 a.tablespace_name=b.tablespace_name 9 ; Tablespace Name Datafile List Total MB Used MB Free MB Usage Percent ------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------------------------------ SYSAUX /u01/app/oracle/oradata/orcl11g/sysaux01.dbf 560 525.25 34.75 93.79 % UNDOTBS1 /u01/app/oracle/oradata/orcl11g/undotbs01.dbf 65 24.125 40.875 37.12 % USERS /u01/app/oracle/oradata/orcl11g/users01.dbf 5 4.0625 .9375 81.25 % SYSTEM /u01/app/oracle/oradata/orcl11g/system01.dbf 720 711.375 8.625 98.8 % EXAMPLE /u01/app/oracle/oradata/orcl11g/example01.dbf 345.625 309.8125 35.8125 89.64 % SQL> SQL> create tablespace adamhuan datafile '/u01/app/oracle/oradata/orcl11g/adamhuan01.dbf' size 3000M; Tablespace created. SQL> SQL> SQL> select 2 b.tablespace_name "Tablespace Name",b.datafilelist "Datafile List",b.totalmb "Total MB",(b.totalmb - a.freemb) "Used MB",a.freemb "Free MB", 3 Round((1- a.freemb/b.totalmb)*100,2)||' %' "Usage Percent" 4 from 5 (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a, 6 (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b 7 where 8 a.tablespace_name=b.tablespace_name 9 ; Tablespace Name Datafile List Total MB Used MB Free MB Usage Percent ------------------------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------------------------------------ SYSAUX /u01/app/oracle/oradata/orcl11g/sysaux01.dbf 560 525.25 34.75 93.79 % UNDOTBS1 /u01/app/oracle/oradata/orcl11g/undotbs01.dbf 65 24.125 40.875 37.12 % USERS /u01/app/oracle/oradata/orcl11g/users01.dbf 5 4.0625 .9375 81.25 % ADAMHUAN /u01/app/oracle/oradata/orcl11g/adamhuan01.dbf 3000 1 2999 .03 % SYSTEM /u01/app/oracle/oradata/orcl11g/system01.dbf 720 711.4375 8.5625 98.81 % EXAMPLE /u01/app/oracle/oradata/orcl11g/example01.dbf 345.625 309.8125 35.8125 89.64 % 6 rows selected. SQL> |
如上所示,我们创建了一个3G的表空间:ADAMHUAN。
它具有一个数据文件,位于:
/u01/app/oracle/oradata/orcl11g/adamhuan01.dbf
此时,文件系统的使用情况是:
1 2 3 4 5 6 7 8 9 10 |
SQL> !df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 18G 7.8G 8.6G 48% / /dev/sda1 289M 17M 258M 7% /boot tmpfs 500M 236M 265M 48% /dev/shm /dev/sr0 4.1G 4.1G 0 100% /iso /dev/mapper/vg_oracle-lv_oracle 19G 12G 5.6G 69% /u01 SQL> |
如上所示,/u01挂载点还有5.6G的剩余空间,足够导出的DMP文件的存放。
创建一个全新的SCHEMA:ADAMHUAN,该SCHEMA会作为EXP的导出对象。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
SQL> select username,default_tablespace,account_status from dba_users order by 3; USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS ----------------------- ----------------------- ----------------------- XDB SYSAUX EXPIRED & LOCKED SPATIAL_CSW_ADMIN_USR USERS EXPIRED & LOCKED HR USERS EXPIRED & LOCKED APEX_PUBLIC_USER USERS EXPIRED & LOCKED OE USERS EXPIRED & LOCKED OUTLN SYSTEM EXPIRED & LOCKED FLOWS_FILES SYSAUX EXPIRED & LOCKED MDSYS SYSAUX EXPIRED & LOCKED ORDSYS SYSAUX EXPIRED & LOCKED EXFSYS SYSAUX EXPIRED & LOCKED WMSYS SYSAUX EXPIRED & LOCKED APPQOSSYS SYSAUX EXPIRED & LOCKED APEX_030200 SYSAUX EXPIRED & LOCKED OWBSYS_AUDIT SYSAUX EXPIRED & LOCKED ORDDATA SYSAUX EXPIRED & LOCKED CTXSYS SYSAUX EXPIRED & LOCKED ANONYMOUS SYSAUX EXPIRED & LOCKED SPATIAL_WFS_ADMIN_USR USERS EXPIRED & LOCKED ORDPLUGINS SYSAUX EXPIRED & LOCKED OWBSYS SYSAUX EXPIRED & LOCKED SI_INFORMTN_SCHEMA SYSAUX EXPIRED & LOCKED OLAPSYS SYSAUX EXPIRED & LOCKED SCOTT USERS EXPIRED & LOCKED ORACLE_OCM USERS EXPIRED & LOCKED XS$NULL USERS EXPIRED & LOCKED BI USERS EXPIRED & LOCKED PM USERS EXPIRED & LOCKED MDDATA USERS EXPIRED & LOCKED IX USERS EXPIRED & LOCKED SH USERS EXPIRED & LOCKED DIP USERS EXPIRED & LOCKED SYSMAN SYSAUX OPEN DBSNMP SYSAUX OPEN SYSTEM SYSTEM OPEN SYS SYSTEM OPEN MGMT_VIEW SYSTEM OPEN 36 rows selected. SQL> SQL> select username,default_tablespace,account_status from dba_users where username='ADAMHUAN'; no rows selected SQL> SQL> create user adamhuan identified by oracle account unlock default tablespace adamhuan; User created. SQL> select username,default_tablespace,account_status from dba_users where username='ADAMHUAN'; USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS ----------------------- ----------------------- ----------------------- ADAMHUAN ADAMHUAN OPEN SQL> SQL> grant resource,connect to adamhuan; Grant succeeded. SQL> |
为SCHEMA:ADAMHUAN,添加数据。
添加数据前:
1 2 3 4 5 6 7 |
SQL> select count(*) from dba_objects where owner='ADAMHUAN'; COUNT(*) ---------- 0 SQL> |
开始添加:
a) 普通的数据表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> show user USER is "SYS" SQL> create table adamhuan.network 2 ( 3 id number(3), 4 name varchar2(400), 5 location varchar2(400), 6 description varchar2(400) 7 ); Table created. SQL> SQL> insert into adamhuan.network values (0,'Bili','Shang Hai','Enginer'); 1 row created. SQL> SQL> insert into adamhuan.network values (1,'Maybe Pictual','Wu Han','Info Base'); 1 row created. SQL> insert into adamhuan.network values (2,'Maybe Software','Guang Zhou','Hello world'); 1 row created. SQL> commit; Commit complete. SQL> SQL> col name for a18 SQL> col location for a15 SQL> col description for a16 SQL> select * from adamhuan.network; ID NAME LOCATION DESCRIPTION ---------- ------------------ --------------- ---------------- 0 Bili Shang Hai Enginer 1 Maybe Pictual Wu Han Info Base 2 Maybe Software Guang Zhou Hello world SQL> |
b) 大对象:程序
创建数据表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> create table adamhuan.adamhuan_software 2 ( 3 id number, 4 name varchar2(30), 5 binary_data blob 6 ); Table created. SQL> SQL> create directory adamhuan_soft as '/software/Software'; Directory created. SQL> grant read,write on directory adamhuan_soft to adamhuan; Grant succeeded. SQL> |
创建导入脚本,并导入:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 |
[root@ora11g Software]# cat /script/change_file_name_in_sequence.sh echo "----------------------------" echo "Change File Name" echo "Start: "`date` echo "----------------------------" count=1 file_list=`ls` for file_item in $file_list do orig_path=`pwd $file_item`"/$file_item" new_path=`pwd $file_item`"/"$count".data" echo "==========================================" echo "### Orig: "$orig_path echo "### New: "$new_path mv $orig_path $new_path; echo "### Done." count=$(($count+1)) done echo "----------------------------" echo "End: "`date` echo "----------------------------" [root@ora11g Software]# [root@ora11g Software]# pwd /software/Software [root@ora11g Software]# ll total 382704 -rw-r--r-- 1 root root 215688716 Oct 9 12:21 abc.mp4 -rw-r--r-- 1 root root 54472960 Oct 9 12:21 W.P.S.4842.12012.0.exe -rw-r--r-- 1 root root 121319464 Oct 9 12:21 xampp-win32-1.8.3-0-VC11-installer.exe [root@ora11g Software]# [root@ora11g Software]# sh /script/change_file_name_in_sequence.sh ---------------------------- Change File Name Start: Thu Oct 9 12:24:52 CST 2014 ---------------------------- ========================================== ### Orig: /software/Software/abc.mp4 ### New: /software/Software/1.data ### Done. ========================================== ### Orig: /software/Software/W.P.S.4842.12012.0.exe ### New: /software/Software/2.data ### Done. ========================================== ### Orig: /software/Software/xampp-win32-1.8.3-0-VC11-installer.exe ### New: /software/Software/3.data ### Done. ---------------------------- End: Thu Oct 9 12:24:52 CST 2014 ---------------------------- [root@ora11g Software]# [root@ora11g Software]# ll total 382704 -rw-r--r-- 1 root root 215688716 Oct 9 12:21 1.data -rw-r--r-- 1 root root 54472960 Oct 9 12:21 2.data -rw-r--r-- 1 root root 121319464 Oct 9 12:21 3.data [root@ora11g Software]# 导入前,数据库查询: SQL> select * from adamhuan.adamhuan_software; no rows selected SQL> 导入阶段: [oracle@ora11g Software]$ cat /script/insert_into_oracle_bfile.sh echo "----------------------------" echo "Insert File into Oracle BFile" echo "Start: "`date` echo "----------------------------" oracle_sid=`env | grep --color ORACLE_SID` echo "----------------------------" # Define a Counter count=1 # Get a list of file file_list=`ls` for file_item in $file_list do echo "==========================================" echo "### File you want to input: "$file_item value_insert=$count",'"$count".data',empty_blob()" file_name_bfile="'"$count".data'" sqlplus / as sysdba <<EOF select '$count' from dual; declare src_bfile bfile; des_blob blob; begin insert into adamhuan.adamhuan_software values($value_insert) return binary_data into des_blob; src_bfile := bfilename('ADAMHUAN_SOFT',$file_name_bfile); dbms_lob.open(src_bfile,dbms_lob.file_readonly); dbms_lob.loadfromfile(des_blob,src_bfile,dbms_lob.getlength(src_bfile)); dbms_lob.close(src_bfile); commit; end; / exit EOF echo "### Done." count=$(($count+1)) done echo "----------------------------" echo "End: "`date` echo "----------------------------" [oracle@ora11g Software]$ [oracle@ora11g Software]$ pwd /software/Software [oracle@ora11g Software]$ ll total 382704 -rw-r--r-- 1 root root 215688716 Oct 9 12:21 1.data -rw-r--r-- 1 root root 54472960 Oct 9 12:21 2.data -rw-r--r-- 1 root root 121319464 Oct 9 12:21 3.data [oracle@ora11g Software]$ [oracle@ora11g Software]$ sh /script/insert_into_oracle_bfile.sh ---------------------------- Insert File into Oracle BFile Start: Thu Oct 9 12:27:05 CST 2014 ---------------------------- ---------------------------- ========================================== ### File you want to input: 1.data SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:27:05 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 1 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 2.data SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:27:20 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 2 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 3.data SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:27:22 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 3 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ---------------------------- End: Thu Oct 9 12:27:29 CST 2014 ---------------------------- [oracle@ora11g Software]$ [oracle@ora11g Software]$ 导入后,查看数据库的数据表的状态: SQL> run 1* select * from adamhuan.adamhuan_software ID NAME ---------- ------------------ BINARY_DATA -------------------------------------------------------------------------------- 1 1.data 000000146674797069736F6D0000000169736F6D0015280C6D6F6F760000006C6D76686400000000 D04D81D8D04D81D80000025800187F73000100000100000000000000000000000001000000000000 2 2.data 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000 0000000000000000000000000000000000000000D80000000E1FBA0E00B409CD21B8014CCD215468 3 3.data ID NAME ---------- ------------------ BINARY_DATA -------------------------------------------------------------------------------- 4D5A90000300000004000000FFFF0000B80000000000000040000000000000000000000000000000 0000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD215468 SQL> |
c) 大对象:图片
创建数据表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> create table adamhuan.adamhuan_pic 2 ( 3 id number, 4 name varchar2(30), 5 binary_data blob 6 ); Table created. SQL> SQL> select * from adamhuan.adamhuan_pic; no rows selected SQL> |
创建目录对象:
1 2 3 4 5 6 7 8 9 |
SQL> create directory adamhuan_pic as '/software/Pictural'; Directory created. SQL> grant read,write on directory adamhuan_pic to adamhuan; Grant succeeded. SQL> |
导入脚本(批量导入某个路径下的文件到数据库的BLOB):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
[root@ora11g Pictural]# cat /script/insert_into_oracle_bfile.sh echo "----------------------------" echo "Insert File into Oracle BLOB" echo "Start: "`date` echo "----------------------------" oracle_sid=`env | grep --color ORACLE_SID` echo "----------------------------" # Define a Counter count=1 # Get a list of file file_list=`ls` for file_item in $file_list do echo "==========================================" echo "### File you want to input: "$file_item value_insert=$count",'"$count".jpg',empty_blob()" file_name_bfile="'"$count".jpg'" sqlplus / as sysdba <<EOF select '$count' from dual; declare src_bfile bfile; des_blob blob; begin insert into adamhuan.adamhuan_pic values($value_insert) return binary_data into des_blob; src_bfile := bfilename('ADAMHUAN_PIC',$file_name_bfile); dbms_lob.open(src_bfile,dbms_lob.file_readonly); dbms_lob.loadfromfile(des_blob,src_bfile,dbms_lob.getlength(src_bfile)); dbms_lob.close(src_bfile); commit; end; / exit EOF echo "### Done." count=$(($count+1)) done echo "----------------------------" echo "End: "`date` echo "----------------------------" [root@ora11g Pictural]# |
导入前:
1 2 3 4 5 |
SQL> select * from adamhuan.adamhuan_pic; no rows selected SQL> |
开始导入:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
[oracle@ora11g Pictural]$ ll total 16340 -rwxrwxrwx 1 root root 3135741 Oct 9 08:57 1.jpg -rwxrwxrwx 1 root root 3135741 Oct 9 08:57 2.jpg -rwxrwxrwx 1 root root 2766824 Oct 9 08:57 3.jpg -rwxrwxrwx 1 root root 2303872 Oct 9 08:57 4.jpg -rwxrwxrwx 1 root root 2383037 Oct 9 08:57 5.jpg -rwxrwxrwx 1 root root 2973218 Oct 9 08:57 6.jpg [oracle@ora11g Pictural]$ [oracle@ora11g Pictural]$ sh /script/insert_into_oracle_bfile.sh ---------------------------- Insert File into Oracle BLOB Start: Thu Oct 9 12:13:32 CST 2014 ---------------------------- ---------------------------- ========================================== ### File you want to input: 1.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 1 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 2.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 2 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 3.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 3 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 4.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 4 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 5.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 5 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ========================================== ### File you want to input: 6.jpg SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 9 12:13:32 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> ' - 6 SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 PL/SQL procedure successfully completed. SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ### Done. ---------------------------- End: Thu Oct 9 12:13:32 CST 2014 ---------------------------- [oracle@ora11g Pictural]$ [oracle@ora11g Pictural]$ |
导入后:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> run 1* select * from adamhuan.adamhuan_pic ID NAME ---------- ------------------ BINARY_DATA -------------------------------------------------------------------------------- 1 1.jpg 474946383961F401C800F700001B18160B03020C0B0A3726233320191312122914112A1E1B1C0707 D8C5C30F100E232120D4BCB946393629262545251C221D1A462A26A8968A383229C7B4A9B8A499BB 2 2.jpg 474946383961F401C800F700001B18160B03020C0B0A3726233320191312122914112A1E1B1C0707 D8C5C30F100E232120D4BCB946393629262545251C221D1A462A26A8968A383229C7B4A9B8A499BB 3 3.jpg ID NAME ---------- ------------------ BINARY_DATA -------------------------------------------------------------------------------- 474946383961A4013B0187C000010000825A0D8C8B90AD984B3D5F22B1BCD9D2A58F8A6553D4992F 400B033B506CC5965BAE5F0C152540E7D487B1B49EDCDDDDB47C54142F01C9BB6253627D41382F4F 4 4.jpg 474946383961F4010E01E70000ECCFB2D4B298B0968DDFA211AA784F704A2FEFDAC86E564BCB986F DDC4B1845532776556B38555D2A87D988675564D468F2E0DFFFDEBBCA595F3D1624A2B134D36272E 5 5.jpg 4749463839619001E10087B400090E11900B0A1625854175148C83352E8893959CA44112102B91CA ID NAME ---------- ------------------ BINARY_DATA -------------------------------------------------------------------------------- D096A50F24465D8C8D2D3BD661741799C79F45494F215D8790C949598D511D454E3EC7C964BC961A 6 6.jpg 4749463839614001F000F700000B06040E09100F10071608061C0B101D10091C1110250A06260C10 2811092B1511350D06340E103714093718123A1E202D200D2D20163A210D3B22173D301D3C272143 6 rows selected. SQL> |
至此,数据的准备,初步完成。
二、导出阶段:导出信息确认
先查看我们要导出的数据量。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
SQL> set linesize 300 SQL> col owner for a12 SQL> col object_type for a12 SQL> col object_name for a30 SQL> select owner,object_type,object_name,object_id,status,to_char(created,'yyyy-mon-dd hh24:mi:ss'),to_char(last_ddl_time,'yyyy-mon-dd hh24:mi:ss') from dba_objects where owner='ADAMHUAN'; OWNER OBJECT_TYPE OBJECT_NAME OBJECT_ID STATUS TO_CHAR(CREATED,'YYY TO_CHAR(LAST_DDL_TIM ------------ ------------ ------------------------------ ---------- ------- -------------------- -------------------- ADAMHUAN TABLE ADAMHUAN_SOFTWARE 76862 VALID 2014-oct-09 17:50:15 2014-oct-09 17:50:15 ADAMHUAN INDEX SYS_IL0000076862C00003$$ 76864 VALID 2014-oct-09 17:50:15 2014-oct-09 17:50:15 ADAMHUAN TABLE NETWORK 76861 VALID 2014-oct-09 17:45:48 2014-oct-09 17:45:48 ADAMHUAN LOB SYS_LOB0000076862C00003$$ 76863 VALID 2014-oct-09 17:50:15 2014-oct-09 17:50:15 ADAMHUAN TABLE ADAMHUAN_PIC 76867 VALID 2014-oct-09 18:14:24 2014-oct-09 18:14:24 ADAMHUAN INDEX SYS_IL0000076867C00003$$ 76869 VALID 2014-oct-09 18:14:24 2014-oct-09 18:14:24 ADAMHUAN LOB SYS_LOB0000076867C00003$$ 76868 VALID 2014-oct-09 18:14:24 2014-oct-09 18:14:24 7 rows selected. SQL> SQL> select object_type,count(*) from dba_objects where owner='ADAMHUAN' group by object_type; OBJECT_TYPE COUNT(*) ------------ ---------- LOB 2 TABLE 3 INDEX 2 SQL> SQL> select sum(bytes)/1024/1024 from dba_segments where owner='ADAMHUAN'; SUM(BYTES)/1024/1024 -------------------- 1414.25 SQL> SQL> select segment_type,count(*),sum(bytes)/1024/1024 from dba_segments where owner='ADAMHUAN' group by segment_type order by segment_type; SEGMENT_TYPE COUNT(*) SUM(BYTES)/1024/1024 ------------------ ---------- -------------------- LOBINDEX 2 2.0625 LOBSEGMENT 2 1412 TABLE 3 .1875 SQL> |
上述“SUM”函数的最后显示单位为:MB
文件系统中,通过大对象导入的源文件的大小:
1 2 3 4 5 6 7 8 9 10 11 12 |
[ora11g@rhel5u10 software]$ pwd /software [ora11g@rhel5u10 software]$ ll total 156 drwxrwxrwx 2 ora11g oinstall 4096 Oct 9 18:15 Pictural drwxrwxrwx 2 ora11g oinstall 4096 Oct 9 17:59 Software -rwxrwxrwx 1 root root 146185 Oct 6 00:10 vsftpd-2.0.5-28.el5.x86_64.rpm [ora11g@rhel5u10 software]$ du -sh Pictural/ 3.5M Pictural/ [ora11g@rhel5u10 software]$ du -sh Software/ 1.4G Software/ [ora11g@rhel5u10 software]$ |
1.4G*1024=1433.6MB
跟上述SQL(select segment_type,count(*),sum(bytes)/1024/1024 from dba_segments …)查出来的差不多。
当前,数据库表空间的使用量:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> col "Tablespace Name" for a12 SQL> col "Datafile List" for a50 SQL> col "Usage Percent" for a12 SQL> run 1 select 2 b.tablespace_name "Tablespace Name",b.datafilelist "Datafile List",b.totalmb "Total MB",(b.totalmb - a.freemb) "Used MB",a.freemb "Free MB", 3 Round((1- a.freemb/b.totalmb)*100,2)||' %' "Usage Percent" 4 from 5 (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a, 6 (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b 7 where 8 a.tablespace_name=b.tablespace_name 9* Tablespace N Datafile List Total MB Used MB Free MB Usage Percen ------------ -------------------------------------------------- ---------- ---------- ---------- ------------ SYSAUX /u01/app/oracle/oradata/orcl11g/sysaux01.dbf 560 526.1875 33.8125 93.96 % UNDOTBS1 /u01/app/oracle/oradata/orcl11g/undotbs01.dbf 65 29.3125 35.6875 45.1 % USERS /u01/app/oracle/oradata/orcl11g/users01.dbf 5 4.0625 .9375 81.25 % ADAMHUAN /u01/app/oracle/oradata/orcl11g/adamhuan01.dbf 3000 1415.25 1584.75 47.18 % SYSTEM /u01/app/oracle/oradata/orcl11g/system01.dbf 720 711.4375 8.5625 98.81 % EXAMPLE /u01/app/oracle/oradata/orcl11g/example01.dbf 345.625 309.8125 35.8125 89.64 % 6 rows selected. SQL> |
可以看到,ADAMHUAN表空间的使用量为:47.18 %
当前操作系统的挂载点使用量:
1 2 3 4 5 6 7 8 9 |
[ora11g@rhel5u10 software]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 18G 4.3G 13G 27% / /dev/sda1 289M 17M 258M 7% /boot tmpfs 500M 236M 265M 48% /dev/shm /dev/sr0 4.1G 4.1G 0 100% /iso /dev/mapper/vg_oracle-lv_oracle 19G 14G 4.1G 77% /u01 [ora11g@rhel5u10 software]$ |
可以看到,根分区(“/”)以及“/u01”的剩余容量(Avail)都是充足的。
要导出的对象:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> run 1* select owner,table_name,tablespace_name,status from dba_tables where owner='ADAMHUAN' OWNER TABLE_NAME TABLESPACE_N STATUS ------------ -------------------- ------------ -------- ADAMHUAN NETWORK ADAMHUAN VALID ADAMHUAN ADAMHUAN_SOFTWARE ADAMHUAN VALID ADAMHUAN ADAMHUAN_PIC ADAMHUAN VALID SQL> SQL> select * from adamhuan.network; ID NAME LOCATION DESCRIPTION ---------- ----------------------- ----------------------- ----------------------- 0 adamhuan wuhan IT Guy 1 allah yuan guangxi Singer 2 kingkingheart wuhan CEO 3 jonathan wuhan chicken SQL> select * from adamhuan.adamhuan_software; ID NAME BINARY_DATA ---------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1.data 23212F7573722F62696E2F656E7620626173680A230A2320564D7761726520496E7374616C6C6572204C61756E636865720A230A232054686973206973207468652065786563757461626C6520737475 2 2.data 23212F7573722F62696E2F656E7620626173680A230A2320564D7761726520496E7374616C6C6572204C61756E636865720A230A232054686973206973207468652065786563757461626C6520737475 3 3.data 000000146674797069736F6D0000000169736F6D000AB2666D6F6F760000006C6D76686400000000D0577E74D0577E7400000258000D2ECC000100000100000000000000000000000001000000000000 4 4.data 000000146674797069736F6D0000000169736F6D000D33D06D6F6F760000006C6D76686400000000D055ED7FD055ED7F00000258000D7A1F000100000100000000000000000000000001000000000000 5 5.data 000000186674797069736F6D0000000169736F6D61766331000AEF0E6D6F6F760000006C6D76686400000000D059EF7CD059EF7C00000258000D23330001000001000000000000000000000000010000 SQL> select * from adamhuan.adamhuan_pic; ID NAME BINARY_DATA ---------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1.jpg FFD8FFE000104A46494600010100000100010000FFFE003B43524541544F523A2067642D6A7065672076312E3020287573696E6720494A47204A50454720763632292C207175616C697479203D203930 2 2.jpg FFD8FFE000104A46494600010100000100010000FFFE003B43524541544F523A2067642D6A7065672076312E3020287573696E6720494A47204A50454720763632292C207175616C697479203D203930 3 3.jpg FFD8FFE000104A46494600010100000100010000FFFE003B43524541544F523A2067642D6A7065672076312E3020287573696E6720494A47204A50454720763632292C207175616C697479203D203930 4 4.jpg FFD8FFE122044578696600004D4D002A00000008000E01000003000000011110000001010003000000010B6000000102000300000003000000B6010600030000000100020000010F0002000000060000 5 5.jpg FFD8FFE000104A46494600010100000100010000FFFE003B43524541544F523A2067642D6A7065672076312E3020287573696E6720494A47204A50454720763632292C207175616C697479203D203930 6 6.jpg FFD8FFE11BAE4578696600004D4D002A00000008000C01000003000000010780000001010003000000010438000001020003000000030000009E01060003000000010002000001120003000000010001 6 rows selected. SQL> |
三、导出阶段:开始导出
1.创建导出路径
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@rhel5u10 /]# pwd / [root@rhel5u10 /]# mkdir /export [root@rhel5u10 /]# ll -d /export drwxr-xr-x 2 root root 4096 Oct 9 18:44 /export [root@rhel5u10 /]# chown ora11g.oinstall /export/ [root@rhel5u10 /]# ll -d /export drwxr-xr-x 2 ora11g oinstall 4096 Oct 9 18:44 /export [root@rhel5u10 /]# [root@rhel5u10 /]# su - ora11g [ora11g@rhel5u10 ~]$ cd /export/ [ora11g@rhel5u10 export]$ ll total 0 [ora11g@rhel5u10 export]$ touch me [ora11g@rhel5u10 export]$ ll total 0 -rw-r--r-- 1 ora11g oinstall 0 Oct 9 18:45 me [ora11g@rhel5u10 export]$ |
2.环境变量:NLS_LANG
查询数据库实例的字符集设置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SQL> run 1* select * from nls_database_parameters PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8MSWIN1252 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 11.2.0.3.0 20 rows selected. SQL> |
通过上述信息,可以得到NLS_LANG的设置:
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
3.关于导出
(在该阶段,关注UNDO表空间的变化)
第一种导出:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
[ora11g@rhel5u10 export]$ ll total 0 -rw-r--r-- 1 ora11g oinstall 0 Oct 9 18:45 me [ora11g@rhel5u10 export]$ [ora11g@rhel5u10 export]$ env | grep --color SID ORACLE_SID=orcl11g [ora11g@rhel5u10 export]$ env | grep --color NLS_LANG NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 [ora11g@rhel5u10 export]$ [ora11g@rhel5u10 export]$ exp system/oracle owner=adamhuan rows=y compress=y file=/export/purt_exp_without_user_access-base_exp_option.dmp Export: Release 11.2.0.3.0 - Production on Thu Oct 9 19:46:14 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user ADAMHUAN . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user ADAMHUAN About to export ADAMHUAN's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export ADAMHUAN's tables via Conventional Path ... . . exporting table ADAMHUAN_PIC 6 rows exported . . exporting table ADAMHUAN_SOFTWARE 5 rows exported . . exporting table NETWORK 4 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. [ora11g@rhel5u10 export]$ [ora11g@rhel5u10 export]$ ll total 1404536 -rw-r--r-- 1 ora11g oinstall 0 Oct 9 18:45 me -rw-r--r-- 1 ora11g oinstall 1436835840 Oct 9 19:48 purt_exp_without_user_access-base_exp_option.dmp [ora11g@rhel5u10 export]$ du -sh * 0 me 1.4G purt_exp_without_user_access-base_exp_option.dmp [ora11g@rhel5u10 export]$ |
上述导出的过程中,没有用户连入数据库操作。
导出顺利完成。
第二种导出:
导出过程中,有用户连入数据库做操作。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[ora11g@rhel5u10 export]$ exp system/oracle owner=adamhuan rows=y compress=y file=/export/purt_exp_with_user_access-base_exp_option.dmp Export: Release 11.2.0.3.0 - Production on Thu Oct 9 19:52:47 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified users ... . exporting pre-schema procedural objects and actions . exporting foreign function library names for user ADAMHUAN . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user ADAMHUAN About to export ADAMHUAN's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export ADAMHUAN's tables via Conventional Path ... . . exporting table ADAMHUAN_PIC 6 rows exported . . exporting table ADAMHUAN_SOFTWARE 5 rows exported . . exporting table NETWORK 4 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. [ora11g@rhel5u10 export]$ |
上述导出可以看到,导出的adamhuan.adamhuan_software表只有5条记录。
事实上,在操作这条导出的时候,另一个会话中,通过:
sh /script/insert_into_oracle_blob_data.sh
同一时间向数据库的adamhuan.adamhuan_software表中添加记录。
在该场景中,UNDO表空间的使用率有明显的上升,如图所示:
### 当前数据库中与UNDO有关的参数
1 2 3 4 5 6 7 8 |
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> |
最后,导出成功的时候,事实上,数据表中已经有十条记录了:
1 2 3 4 5 6 7 |
SQL> select count(*) from adamhuan.adamhuan_software; COUNT(*) ---------- 10 SQL> |
导出的5条记录仅为发起exp时的adamhuan.adamhuan_software状态。
虽然导出成功,但是EXP过程中遇到的快照太旧的错误没有模拟出来。
这是很可惜的。
还需要更多的思考,以及对基础的巩固。
——————————————————
Ending。