1. EXPDP/IMPDP方式 SQL create user zlm identified by zlm; User created. SQL grant connect,resource to zlm; Grant succeeded. SQL create tablespace ts_zlm datafile /u01/app/oracle/oradata/ora10g/zlm01.dbf size 100M reuse; Tablespace created.
1. EXPDP/IMPDP方式SQL> create user zlm identified by zlm;
User created.
SQL> grant connect,resource to zlm;
Grant succeeded.
SQL> create tablespace ts_zlm datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 100M reuse;
Tablespace created.
SQL> alter user zlm default tablespace ts_zlm;
User altered.
SQL> select username,default_tablespace from dba_users where username='ZLM';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ZLM TS_ZLM
SQL> col name for a50
SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';
NAME BYTES/1024/1024
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 5
SQL> set lin 120 pages 120
SQL> col username for a8
SQL> col name for a45
SQL> select a.username,a.default_tablespace,b.name from dba_users a,v$datafile b,v$tablespace c where a.default_tablespace=c.name and b.ts#=c.ts# and a.username='SCOTT';
USERNAME DEFAULT_TABLESPACE NAME
-------- ------------------------------ ---------------------------------------------
SCOTT USERS /u01/app/oracle/oradata/ora10g/users01.dbf
SQL> alter database datafile '/u01/app/oracle/oradata/ora10g/users01.dbf' resize 100M;
Database altered.
SQL> select name,bytes/1024/1024 from v$datafile where name like '%users01.dbf';
NAME BYTES/1024/1024
--------------------------------------------- ---------------
/u01/app/oracle/oradata/ora10g/users01.dbf 100
SQL> select owner,directory_name from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
SYS DATA_PUMP_DIR
SYS SUBDIR
SYS XMLDIR
SYS MEDIA_DIR
SYS LOG_FILE_DIR
SYS DATA_FILE_DIR
SYS WORK_DIR
SYS ADMIN_DIR
SQL> create directory zlm_pump as '/u01/expdp';
Directory created.
SQL> !mkdir /u01/expdp
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> grant connect,resource to scott;
Grant succeeded.
SQL> grant read,write on directory zlm_pump to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table zlm1 as select * from dba_objects;
Table created.
SQL> insert into zlm1 select * from dba_objects;
50318 rows created.
SQL> /
50318 rows created.
SQL> select count(*) from zlm1;
COUNT(*)
----------
150954
SQL> select table_name from user_tables where tablespace_name='USERS';
TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT
[oracle@ora10g ~]$ expdp scott/tiger parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users
Export: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:35:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/******** parallel=2 directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp tablespaces=users
Estimate in progress using BLOCKS method...--可以通过estimate=block(默认值)指定,还有一个是statistic
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 17.18 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."ZLM1" 14.06 MB 150954 rows
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLESPACE_01 is:
/u01/expdp/scott01.dmp
/u01/expdp/scott02.dmp
Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 14:36:10
[oracle@ora10g ~]$ impdp zlm/zlm parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp
Import: Release 10.2.0.1.0 - Production on Sunday, 31 August, 2014 14:41:30
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ZLM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ZLM"."SYS_IMPORT_FULL_01": zlm/******** parallel=2 remap_schema=scott:zlm remap_tablespace=users:ts_zlm directory=zlm_pump dumpfile=scott01.dmp,scott02.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ZLM"."DEPT" 5.656 KB 4 rows
. . imported "ZLM"."EMP" 7.820 KB 14 rows
. . imported "ZLM"."SALGRADE" 5.585 KB 5 rows
. . imported "ZLM"."BONUS" 0 KB 0 rows
. . imported "ZLM"."ZLM1" 14.06 MB 150954 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ZLM"."SYS_IMPORT_FULL_01" successfully completed at 14:41:43
[oracle@ora10g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 31 14:43:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn zlm/zlm
Connected.
SQL> select table_name from user_tables where tablespace_name='TS_ZLM';
TABLE_NAME
------------------------------
ZLM1
SALGRADE
BONUS
EMP
DEPT
SQL> select object_name from user_objects;
OBJECT_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
ZLM1
PK_DEPT
PK_EMP
SCHEMA SCOTT的表空间USERS中的全部对象已经被导入到SCHEMA ZLM的表空间,包括表和索引
2. EXP/IMP方式
SQL> conn / as sysdba
Connected.
SQL> drop user zlm cascade;
User dropped.
SQL> create user zlm identified by zlm;
User created.
SQL> grant connect,resource to zlm;
Grant succeeded.
SQL> alter user zlm default tablespace ts_zlm;
User altered.
SQL> conn zlm/zlm
Connected.
SQL> select * from cat;
no rows selected
SQL> !
[oracle@ora10g ~]$ exp scott/tiger owner=scott file=/u01/scott01.dmp
Export: Release 10.2.0.1.0 - Production on Sun Aug 31 15:42:00 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. 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 BONUS 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT 4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP 14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE 5 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table ZLM1 150954 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 with warnings.
[oracle@ora10g ~]$ imp zlm/zlm fromuser=scott touser=zlm file=/u01/exp/scott01.dmp
Import: Release 10.2.0.1.0 - Production on Sun Aug 31 15:43:01 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
IMP-00002: failed to open /u01/exp/scott01.dmp for read --路径指定错了
Import file: expdat.dmp > /u01/scott01.dmp
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. . importing table "BONUS" 0 rows imported
. . importing table "DEPT" 4 rows imported
. . importing table "EMP" 14 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "ZLM1" 150954 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[oracle@ora10g ~]$ exit
SQL> col object_name for a15
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
--------------- -------------------
BONUS TABLE
DEPT TABLE
PK_DEPT INDEX
EMP TABLE
PK_EMP INDEX
SALGRADE TABLE
ZLM1 TABLE
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
BONUS USERS
DEPT USERS
EMP USERS
SALGRADE USERS
ZLM1 USERS
注意,用imp导入后的对象,都是存放在exp导出时的表空间的,如果要迁移到别的表空间,就要使用alter table move tablespace来实现,先通过下面的SQL拼接语句来获得批量move的语句: SQL> select 'alter table '||table_name||' MOVE TABLESPACE ts_zlm;' from user_tables;
'ALTERTABLE'||TABLE_NAME||'MOVETABLESPACETS_ZLM;'
------------------------------------------------------------------
alter table BONUS MOVE TABLESPACE ts_zlm;
alter table DEPT MOVE TABLESPACE ts_zlm;
alter table EMP MOVE TABLESPACE ts_zlm;
alter table SALGRADE MOVE TABLESPACE ts_zlm;
alter table ZLM1 MOVE TABLESPACE ts_zlm;
SQL> select 'ALTER INDEX ' ||index_name || ' REBUILD TABLESPACE ts_zlm;' from user_indexes;
'ALTERINDEX'||INDEX_NAME||'REBUILDTABLESPACETS_ZLM;'
---------------------------------------------------------------------
ALTER INDEX PK_EMP REBUILD TABLESPACE ts_zlm;
ALTER INDEX PK_DEPT REBUILD TABLESPACE ts_zlm;
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SALGRADE TS_ZLM
EMP TS_ZLM
ZLM1 TS_ZLM
DEPT TS_ZLM
BONUS TS_ZLM
SQL> select index_name,tablespace_name from user_indexes;
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_EMP TS_ZLM
PK_DEPT TS_ZLM
可以看到,zlm用户imp导入的数据库对象已经全部迁移到TS_ZLM表空间去了
总结:
EXPDP/IPMDP只能在server上运行,其运行效率完全取决于磁盘I/O,而EXP/IMP不仅可以运行在server上,也可以运行在client上,所以除了磁盘I/O的制约因素,还有网络方面的因素。
EXPDP/IMPDP比EXP/IMP性能有很大的提高,其中影响最大的就是paralle,通常设置为CPU的个数,通过该参数可以并行导出,大大提高导出速度,而EXP/IMP是没有这个功能的,最多也就只有一个DIRECT=Y,使导出不用经过SELECT到SGA的BUFFER中,提高的速度是有限的,注意他们之间的语法也有不小的区别,参数不可互相替代。
EXPDP/IMPDP可以通过remap_schema和remap_tablespace直接实现不同用户和表空间之间的数据迁移;而EXP/IMP则稍微麻烦一点,需要ALTER TABLE xxx MOVE TABLESPACE xxx/ALTER INDEX xxx REBUILD TABLESPACE xxx方式来实现。move相当于把表再重建一次,可以修改storage参数(如initial、pctfree、pctincrease等),还可以move到另外的表空间,如果不指定表空间,则在原来的表空间中move,因此做move操作时,目标表空间需要保证有这个表同样大小的剩余空间,整个操作相当于exp/imp(从表空间A导出到表空间B),move完表后要重建索引。
基于以上几种原因,现在做数据逻辑迁移都是用EXPDP/IMPDP而很少再使用EXP/IMP了。

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SublimeText3 Mac version
God-level code editing software (SublimeText3)

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software