XChinux

Oracle导出文件.dmp的导入和使用

# 1. 服务器端安装Oracle11 Express

## 1.1 安装Oracle11

    安装过程中请设置SYS SYSTEM密码为123456

## 1.2 设置Windows环境变量(如果与系统不一致)

    设置NLS_LANG为SIMPLIFIED CHINESE_CHINA.ZHS16GBK

## 1.3 创建用户和表空间、临时表空间

```sql
sql> conn as sysdba;
sql> CREATE USER mydb IDENTIFIED BY 123456
        DEFAULT TABLESPACE myts
        TEMPORARY TABLESPACE myts_temp;
sql> GRANT DBA TO mydb;
```

## 1.4 设置默认字符集为ZHS16GBK

    启动SQL Plus，执行如下操作：
```sql
sql> conn as sysdba;
sql> shutdown immediate;
database closed.
database dismounted.
oracle instance shut down.
sql> startup mount;
oracle instance started.
total system global area 135337420 bytes
fixed size 452044 bytes
variable size 109051904 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
sql> alter system enable restricted session;
system altered.
sql> alter system set job_queue_processes=0;
system altered.
sql> alter system set aq_tm_processes=0;
system altered.
sql> alter database open;
database altered.
sql> alter database character set  internal_use ZHS16GBK;
sql> shutdown immediate;
sql> startup;
```

## 1.5 获取默认导出目录

```
sql> SELECT * FROM dba_directories;
```

    获取到DATA_PUMP_DIR为C:\oraclexe\app\oracle/admin/xe/dpdump/

## 1.6 导入DMP文件

    先将mydb_xxxx.DMP文件复制到上面查到的DATA_PUMP_DIR目录下
    然后执行导入命令：
```
impdp.exe mydb/123456@XE directory=DATA_PUMP_DIR dumpfile=mydb_xxxx.DMP
```

## 1.7 执行查询

    使用mydb用户登录，并执行查询：

```sql
sql> conn as sysdba;
sql > SELECT * FROM AC_PAYMENTBOOKS WHERE rownum <= 2;
```

## 1.8 使用Applicatinn Express

    1. 用浏览器打开https://127.0.0.1:8080
    2. 在界面中切换语言为English，然后点Application Experess，使用mydb登录。
    3. 创建工作空间（都用mydb）
    4. 登录后进入SQL Workshop,即可使用。

# 2. 在PHP服务器上配置Oracle连接

## 2.1 安装InstantClient 11.2

    将官网上下载好的压缩包解压到某个目录,比如为C:\oracle\instantclient_11_2

## 2.2 配置环境变量

    1. `PATH`环境变量设置为`C:\oracle\instantclient_11_2`
    2. `NLS_LANG`环境变量设置为`SIMPLIFIED CHINESE_CHINA.AL32UTF8`
    3. `TNS_ADMIN`环境变量设置为`C:\oracle\instantclient_11_2`

## 2.3 编写tnsnames.ora

    在`C:\oracle\instantclient_11_2`目录下编写tnsnames.ora文件:
```
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
```

## 2.4 修改PHP配置文件

    修改PHP配置文件php.ini，然后将php_pdo_oci.dll加入到扩展中。

## 2.5 PHP连接Oracle

```php
$pdo = new PDO_DB("oci:dbname=XE", "mydb", "123456");
```