oracle@hpws:~$ cd /opt/ora12c/oradata/ oracle@hpws:/opt/ora12c/oradata$ ll total 12 drwxr-x--- 3 oracle oinstall 4096 6?.25 17:55 ./ drwxr-xr-x 11 oracle oinstall 4096 6?.26 08:31 ../ drwxr-x--- 2 oracle oinstall 4096 6?.26 08:07 ora12c/ oracle@hpws:/opt/ora12c/oradata$ cd ora12c/ oracle@hpws:/opt/ora12c/oradata/ora12c$ ll total 24065080 drwxr-x--- 2 oracle oinstall 4096 6?.26 08:07 ./ drwxr-x--- 3 oracle oinstall 4096 6?.25 17:55 ../ -rw-r----- 1 oracle oinstall 10272768 11?.13 15:03 control01.ctl -rw-r----- 1 oracle oinstall 10737426432 11?.13 14:35 data01.dbf -rw-r----- 1 oracle oinstall 5368717312 11?.13 14:35 data02.dbf -rw-r----- 1 oracle oinstall 375529472 11?.13 14:35 example01.dbf -rw-r----- 1 oracle oinstall 314580992 11?.13 14:35 example02.dbf -rw-r----- 1 oracle oinstall 2147491840 11?.13 14:35 indx01.dbf -rw-r----- 1 oracle oinstall 52429312 11?.13 14:29 redo01.log -rw-r----- 1 oracle oinstall 52429312 11?.13 14:30 redo02.log -rw-r----- 1 oracle oinstall 52429312 11?.13 15:02 redo03.log -rw-r----- 1 oracle oinstall 1835016192 11?.13 15:02 sysaux01.dbf -rw-r----- 1 oracle oinstall 828383232 11?.13 14:59 system01.dbf -rw-r----- 1 oracle oinstall 524296192 11?.13 14:35 system02.dbf -rw-r----- 1 oracle oinstall 996155392 11?.13 14:30 temp01.dbf -rw-r----- 1 oracle oinstall 1342185472 11?.13 15:02 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 11?.13 14:35 users01.dbf oracle@hpws:/opt/ora12c/oradata/ora12c$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 13 15:04:27 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select TABLESPACE_NAME from v$tablespace; select TABLESPACE_NAME from v$tablespace * ERROR at line 1: ORA-00904: "TABLESPACE_NAME": invalid identifier SQL> desc v$tablespace; Name Null? Type ----------------------------------------- -------- ---------------------------- TS# NUMBER NAME VARCHAR2(30) INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3) BIGFILE VARCHAR2(3) FLASHBACK_ON VARCHAR2(3) ENCRYPT_IN_BACKUP VARCHAR2(3) CON_ID NUMBER SQL> select NAME from v$tablespace; NAME -------------------------------------------------------------------------------- SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE DATA INDX 8 rows selected. SQL> create tablespace tbs_cpwebid datafile '/opt/ora12c/oradata/ora12c/cpwebid01.dbf' size 5g autoextend off; Tablespace created. SQL> create user cpwebid identified by cpwebid account unlock; User created. SQL> alter user cpwebid default tablespace tbs_cpwebid ; User altered. SQL> grant dba to cpwebid; Grant succeeded. SQL> grant create table to cpwebid; Grant succeeded. SQL> create tablespace tbs_cpserviceid datafile '/opt/ora12c/oradata/ora12c/cpserviceid01.dbf' size 5g autoextend off; Tablespace created. SQL> show container; SP2-0158: unknown SHOW option "container" SQL> create user cpserviceid identified by cpserviceid account unlock; User created. SQL> alter user cpserviceid default tablespace tbs_cpserviceid ; User altered. SQL> grant dba to cpserviceid; Grant succeeded. SQL> grant create table to cpserviceid; Grant succeeded. SQL> quit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options oracle@hpws:/opt/ora12c/oradata/ora12c$ pwd /opt/ora12c/oradata/ora12c oracle@hpws:/opt/ora12c/oradata/ora12c$ ll total 34550864 drwxr-x--- 2 oracle oinstall 4096 11?.13 15:07 ./ drwxr-x--- 3 oracle oinstall 4096 6?.25 17:55 ../ -rw-r----- 1 oracle oinstall 10272768 11?.13 15:09 control01.ctl -rw-r----- 1 oracle oinstall 5368717312 11?.13 15:08 cpserviceid01.dbf -rw-r----- 1 oracle oinstall 5368717312 11?.13 15:06 cpwebid01.dbf -rw-r----- 1 oracle oinstall 10737426432 11?.13 14:35 data01.dbf -rw-r----- 1 oracle oinstall 5368717312 11?.13 14:35 data02.dbf -rw-r----- 1 oracle oinstall 375529472 11?.13 14:35 example01.dbf -rw-r----- 1 oracle oinstall 314580992 11?.13 14:35 example02.dbf -rw-r----- 1 oracle oinstall 2147491840 11?.13 14:35 indx01.dbf -rw-r----- 1 oracle oinstall 52429312 11?.13 14:29 redo01.log -rw-r----- 1 oracle oinstall 52429312 11?.13 14:30 redo02.log -rw-r----- 1 oracle oinstall 52429312 11?.13 15:09 redo03.log -rw-r----- 1 oracle oinstall 1835016192 11?.13 15:05 sysaux01.dbf -rw-r----- 1 oracle oinstall 828383232 11?.13 15:05 system01.dbf -rw-r----- 1 oracle oinstall 524296192 11?.13 14:35 system02.dbf -rw-r----- 1 oracle oinstall 996155392 11?.13 14:30 temp01.dbf -rw-r----- 1 oracle oinstall 1342185472 11?.13 15:05 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 11?.13 14:35 users01.dbf oracle@hpws:/opt/ora12c/oradata/ora12c$ cd oracle@hpws:~$ pwd /home/oracle oracle@hpws:~$ ll total 58956 drwxr-xr-x 46 oracle oinstall 4096 11?.13 14:07 ./ drwxr-xr-x 7 root root 4096 11?. 4 15:19 ../ -rw-r--r-- 1 oracle oinstall 169 6?.25 18:33 afiedt.buf drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:08 backup/ -rwx--x--x 1 oracle oinstall 34490 11?.13 14:07 .bash_history* -rw-r--r-- 1 oracle oinstall 220 6?.25 18:08 .bash_logout -rwxr-xr-x 1 oracle oinstall 1108 6?.26 08:01 .bash_profile* -rwxr-xr-x 1 oracle oinstall 3559 6?.26 07:59 .bashrc* drwx------ 22 oracle oinstall 4096 9?. 3 08:06 .cache/ drwxr-xr-x 23 oracle oinstall 4096 7?.11 17:45 .config/ drwx------ 3 oracle oinstall 4096 6?.25 18:33 .dbus/ drwxr-xr-x 2 oracle oinstall 4096 7?.11 15:06 Desktop/ -rw-r--r-- 1 oracle oinstall 64 6?.25 18:33 .dmrc drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Documents/ drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Downloads/ drwxr-xr-x 3 oracle oinstall 4096 6?.27 16:57 .eclipse/ -r-------- 1 root root 20 6?.27 00:00 .erlang.cookie -rw-r----- 1 root root 302185 6?.27 18:07 erl_crash.dump -rw-r--r-- 1 root root 1566 7?.11 15:08 erlide_debug.txt -rw------- 1 oracle oinstall 16 6?.25 18:12 .esd_auth drwxr-xr-x 8 oracle oinstall 4096 6?.25 18:33 .evolution/ -rw-r--r-- 1 oracle oinstall 179 6?.25 18:12 examples.desktop drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:33 .fontconfig/ drwx------ 4 oracle oinstall 4096 7?.31 14:48 .gconf/ drwx------ 2 oracle oinstall 4096 6?.25 18:33 .gconfd/ -rw-r----- 1 oracle oinstall 0 6?.25 18:33 .gksu.lock drwx------ 7 oracle oinstall 4096 6?.27 16:36 .gnome2/ drwx------ 2 oracle oinstall 4096 6?.25 18:12 .gnome2_private/ drwx------ 2 oracle oinstall 4096 7?.31 14:48 .gnupg/ drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:12 .gstreamer-0.10/ -rw-r--r-- 1 oracle oinstall 142 6?.27 16:54 .gtk-bookmarks drwx------ 2 oracle oinstall 4096 6?.25 18:33 .gvfs/ -rw------- 1 oracle oinstall 4376 7?.31 14:48 .ICEauthority drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:12 .icons/ drwxr-xr-x 3 oracle oinstall 4096 6?.25 18:33 .local/ drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:33 monitor/ drwx------ 4 oracle oinstall 4096 6?.25 18:12 .mozilla/ drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Music/ drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:36 .nautilus/ drwxr-xr-x 3 oracle oinstall 4096 6?.25 18:33 .oracle/ drwxr-x--- 3 oracle oinstall 4096 6?.25 18:12 oradiag_oracle/ -rw-r--r-- 1 oracle oinstall 267 7?.11 17:53 .pam_environment drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Pictures/ -rwxr-xr-x 1 oracle oinstall 700 6?.25 18:12 .profile* drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Public/ drwx------ 2 oracle oinstall 4096 6?.27 16:36 .pulse/ -rw------- 1 oracle oinstall 256 6?.25 18:12 .pulse-cookie -rw------- 1 oracle oinstall 75 6?.27 15:24 .python_history drwx------ 2 oracle oinstall 4096 6?.30 15:48 .remmina/ drwxr-xr-x 2 oracle oinstall 4096 6?.25 17:21 .rpmdb/ drwxr-xr-x 2 oracle oinstall 4096 6?.26 08:04 scripts/ -rw-r--r-- 1 oracle oinstall 66 6?.25 18:33 .selected_editor -rw------- 1 oracle oinstall 102 6?.25 18:12 .sh_history drwx------ 2 oracle oinstall 4096 6?.25 18:12 .ssh/ drwxr-xr-x 3 oracle oinstall 4096 7?.11 15:04 .swt/ drwxr-xr-x 5 oracle oinstall 4096 11?.13 13:48 SYNC_PRO_NEW/ drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Templates/ drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:12 .themes/ drwx------ 4 oracle oinstall 4096 6?.25 18:33 .thumbnails/ drwx------ 2 oracle oinstall 4096 6?.25 18:33 .tsclient/ drwx------ 2 oracle oinstall 4096 6?.25 18:12 .update-notifier/ drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Videos/ -rw------- 1 oracle oinstall 7915 11?.13 14:07 .viminfo drwx------ 2 oracle oinstall 4096 7?.11 15:03 .vnc/ drwxr-xr-x 4 oracle oinstall 4096 7?.11 16:42 workspace/ -rw-r--r-- 1 oracle oinstall 131 7?.10 14:51 .xinputrc -rw-r--r-- 1 oracle oinstall 14 7?.11 15:00 .xsession -rw------- 1 oracle oinstall 59739682 7?.21 09:05 .xsession-errors -rw------- 1 oracle oinstall 4952 6?.25 18:12 .xsession-errors.old drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:12 涓.浇/ drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:33 妗../ oracle@hpws:~$ ls -ltr total 372 drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:08 backup -rw-r--r-- 1 oracle oinstall 179 6?.25 18:12 examples.desktop drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:12 涓.浇 drwxr-x--- 3 oracle oinstall 4096 6?.25 18:12 oradiag_oracle -rw-r--r-- 1 oracle oinstall 169 6?.25 18:33 afiedt.buf drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:33 妗.. drwxr-xr-x 2 oracle oinstall 4096 6?.25 18:33 monitor drwxr-xr-x 2 oracle oinstall 4096 6?.26 08:04 scripts drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Videos drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Templates drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Public drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Pictures drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Music drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Downloads drwxr-xr-x 2 oracle oinstall 4096 6?.27 16:37 Documents -rw-r----- 1 root root 302185 6?.27 18:07 erl_crash.dump drwxr-xr-x 2 oracle oinstall 4096 7?.11 15:06 Desktop -rw-r--r-- 1 root root 1566 7?.11 15:08 erlide_debug.txt drwxr-xr-x 4 oracle oinstall 4096 7?.11 16:42 workspace drwxr-xr-x 5 oracle oinstall 4096 11?.13 13:48 SYNC_PRO_NEW oracle@hpws:~$ imp userid=cpwebid/cpwebid@ora12c fromuser=cpwebid touser=cpwebid log=user_data.log file=/home/oracle/cpweb.dmp Import: Release 12.1.0.1.0 - Production on Thu Nov 13 15:13:19 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. 省略数据库信息 IMP-00003: ORACLE error 959 encountered ORA-00959: tablespace 'TBS_CPWEB' does not exist Import terminated successfully with warnings. oracle@hpws:~$ imp userid=cpserviceid/cpserviceid@ora11g fromuser=cpserviceid touser=cpserviceid log=user_data.log file=/home/oracle/cpservice.dmp Import: Release 12.1.0.1.0 - Production on Thu Nov 13 15:14:13 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. IMP-00058: ORACLE error 12514 encountered ORA-12514: TNS:listener does not currently know of service requested in connect descriptor IMP-00000: Import terminated unsuccessfully oracle@hpws:~$ oracle@hpws:~$ imp userid=cpserviceid/cpserviceid@ora12c fromuser=cpserviceid touser=cpserviceid log=user_data.log file=/home/oracle/cpservice.dmp Import: Release 12.1.0.1.0 - Production on Thu Nov 13 15:14:41 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Export file created by EXPORT:V11.01.00 via conventional path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) export client uses ZHS16GBK character set (possible charset conversion)
省略数据库信息
Import terminated successfully without warnings. oracle@hpws:~$ cpserviceid/cpserviceid@ora12c -su: cpserviceid/cpserviceid@ora12c: No such file or directory oracle@hpws:~$
核心命令
select FILE_NAME,TABLESPACE_NAME from dba_data_files; create tablespace tbs_a datafile '/opt/ora12c/oradata/ora12c/a.dbf' size 5g autoextend off; create tablespace tbs_b datafile '/opt/ora12c/oradata/ora12c/b.dbf' size 5g autoextend off; create user a identified by ua account unlock; create user b identified by ub account unlock; alter user a default tablespace tbs_a ; alter user b default tablespace tbs_b ; grant dba to a; grant dba to b; grant create table to a; grant create table to b; select USERNAME from dba_users where ACCOUNT_STATUS='OPEN' select TABLESPACE_NAME from v$tablespace;
导入导出命令
oracle---导出----------------------------------------------------------------------------- exp a/a@ab file=/home/oracle/a.dmp exp b/b@bb file=/home/oracle/a.dmp oracle导入12导入11----------------------------------------------------------------------- 下载出来编辑 12 修改为11 imp userid=a/a@ab fromuser=a touser=a log=user_data.log file=/home/oracle/a.dmp imp userid=b/b@bb fromuser=b touser=b log=user_data.log file=/home/oracle/b.dmp