Adamhuan's Data Center - 【逻辑驱动数据】
数据玩物、代码屋、1/0游戏：（零和博弈）/ 禅宗意志 / 规则战争 / 解放数据力量 / 技术的飞速发展并没有改变这个世界，因为，这个世界从没有变，它只是越来越趋近于它本来的模样。
密码包含特殊字符是比较常见的情况，比如说，密码包含【@】符号，这时候如果直接拼接会导致与【@TNS】冲突，因此，如果密码包含特殊字符，Oracle常见的命令行的工具的写法是有所区别的；
一、SQL*Plus
登录：
修改密码：
其中【&】字符是Oracle中用[……]
Adamhuan
如果需要查询数据库中已存在的某个表空间在创建时的SQL语句，有两种方法：
一、dbms_metadata
|
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 |
SQL> set long 1000000 SQL> set linesize 400 SQL> set pagesize 300 SQL> col "Tablespace Name" for a18 SQL> col "DDL SQL" for a70 SQL> SQL> select Tablespace_name "Tablespace Name",dbms_metadata.get_ddl('TABLESPACE',tablespace_name) "DDL SQL" from dba_tablespaces; Tablespace Name DDL SQL ------------------ ---------------------------------------------------------------------- SYSTEM CREATE TABLESPACE "SYSTEM" DATAFILE SIZE 524288000 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL ALTER DATABASE DATAFILE '+DATA01/ATLAS/DATAFILE/system.258.862500103' RESIZE 849346560 SYSAUX CREATE TABLESPACE "SYSAUX" DATAFILE SIZE 419430400 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA01/ATLAS/DATAFILE/sysaux.257.862500037' RESIZE 796917760 UNDOTBS1 CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE '+DATA01/ATLAS/DATAFILE/undotbs1.260.862500179' RESIZE 178257920 TEMP CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 218103808 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 USERS CREATE TABLESPACE "USERS" DATAFILE SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO UNDOTBS2 CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 26214400 AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE '+DATA01/ATLAS/DATAFILE/undotbs2.269.862500889' RESIZE 52428800 EXAMPLE CREATE TABLESPACE "EXAMPLE" DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ALTER DATABASE DATAFILE '+DATA01/ATLAS/DATAFILE/example.268.862500283' RESIZE 1304166400 ADAMHUAN CREATE TABLESPACE "ADAMHUAN" DATAFILE SIZE 314572800 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 131072 DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO 8 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 |
SQL> select owner,directory_name,directory_path,origin_con_id from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ------------------ ------------------------ -------------------------------------------------------------------------------- ------------- SYS ORACLE_HOME / 0 SYS ORACLE_BASE / 0 SYS OPATCH_LOG_DIR /u01/app/oracle/product/12/db_1/QOpatch 0 SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12/db_1/QOpatch 0 SYS OPATCH_INST_DIR /u01/app/oracle/product/12/db_1/OPatch 0 SYS DATA_PUMP_DIR /u01/app/oracle/product/12/db_1/rdbms/log/ 0 SYS XSDDIR /u01/app/oracle/product/12/db_1/rdbms/xml/schema 0 SYS MEDIA_DIR /u01/app/oracle/product/12/db_1/demo/schema/product_media/ 0 SYS DATA_FILE_DIR /u01/app/oracle/product/12/db_1/demo/schema/sales_history/ 0 SYS LOG_FILE_DIR /u01/app/oracle/product/12/db_1/demo/schema/log/ 0 SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12/db_1/ccr/state 0 SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12/db_1/ccr/hosts/ora12c1/state 0 SYS XMLDIR /u01/app/oracle/product/12/db_1/rdbms/xml 0 SYS SS_OE_XMLDIR /u01/app/oracle/product/12/db_1/demo/schema/order_entry/ 0 SYS SUBDIR /u01/app/oracle/product/12/db_1/demo/schema/order_entry//2002/Sep 0 15 rows selected. SQL> SQL> create or replace directory expdp_me as '/home/oracle'; Directory created. SQL> select owner,directory_name,directory_path,origin_con_id from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ------------------ ------------------------ -------------------------------------------------------------------------------- ------------- SYS ORACLE_HOME / 0 SYS ORACLE_BASE / 0 SYS OPATCH_LOG_DIR /u01/app/oracle/product/12/db_1/QOpatch 0 SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12/db_1/QOpatch 0 SYS OPATCH_INST_DIR /u01/app/oracle/product/12/db_1/OPatch 0 SYS DATA_PUMP_DIR /u01/app/oracle/product/12/db_1/rdbms/log/ 0 SYS XSDDIR /u01/app/oracle/product/12/db_1/rdbms/xml/schema 0 SYS MEDIA_DIR /u01/app/oracle/product/12/db_1/demo/schema/product_media/ 0 SYS DATA_FILE_DIR /u01/app/oracle/product/12/db_1/demo/schema/sales_history/ 0 SYS LOG_FILE_DIR /u01/app/oracle/product/12/db_1/demo/schema/log/ 0 SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12/db_1/ccr/state 0 SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12/db_1/ccr/hosts/ora12c1/state 0 SYS EXPDP_ME /home/oracle 0 SYS XMLDIR /u01/app/oracle/product/12/db_1/rdbms/xml 0 SYS SS_OE_XMLDIR /u01/app/oracle/product/12/db_1/demo/schema/order_entry/ 0 SYS SUBDIR /u01/app/oracle/product/12/db_1/demo/schema/order_entry//2002/Sep 0 16 rows selected. SQL> |
导出DU[……]
Adamhuan
EXPDP是Oracle提供的一个数据导出工具。
下面的呈现仅为最基本的导出方式：
|
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 |
[oracle@ora11g ~]$ expdp userid=\"/ as sysdba\" schema=scott LRM-00101: unknown parameter name 'schema' [oracle@ora11g ~]$ expdp userid=\"/ as sysdba\" schemas=scott flashback_time=\"to_timestamp(to_char(sysdate,'yyyy-mon-dd hh24:mi:ss'),'yyyy-mon-dd hh24:mi:ss')\" -bash: syntax error near unexpected token `(' [oracle@ora11g ~]$ expdp userid=\"/ as sysdba\" schemas=scott Export: Release 11.2.0.3.0 - Production on Thu Oct 9 22:22:21 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 Starting "SYS"."SYS_EXPORT_SCHEMA_01": userid="/******** AS SYSDBA" schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SCOTT"."DEPT" 5.937 KB 4 rows . . exported "SCOTT"."EMP" 8.570 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.867 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/orcl/dpdump/expdat.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:23:03 [oracle@ora11g ~]$ [oracle@ora11g ~]$ du -sh /u01/app/oracle/admin/orcl/dpdump/expdat.dmp 244K /u01/app/oracle/admin/orcl/dpdump/expdat.dmp [oracle@ora11g ~]$ |
————————————————————————————————————
Ending。[……]
Adamhuan
关于“expdp”的NETWORK_LINK参数，它通常用于将远端的数据库的数据导出到本地机器的场景。
它的正常使用需要依赖TNS、DBLINK。
本文会呈现EXPDP的NETWORK_LINK使用的技术细节。
——————————————————————
环境说明。
源：
OS:LIN[……]
Adamhuan