首页 > 代码库 > oracle 在已有实力上创建用户 并导入数据

oracle 在已有实力上创建用户 并导入数据

oracle@hpws:~$ cd /opt/ora12c/oradata/oracle@hpws:/opt/ora12c/oradata$ lltotal 12drwxr-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$ lltotal 24065080drwxr-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.dbforacle@hpws:/opt/ora12c/oradata/ora12c$ sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thu Nov 13 15:04:27 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> select TABLESPACE_NAME from v$tablespace;select TABLESPACE_NAME from v$tablespace       *ERROR at line 1:ORA-00904: "TABLESPACE_NAME": invalid identifierSQL> 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                         NUMBERSQL> select NAME from v$tablespace;NAME--------------------------------------------------------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPEXAMPLEDATAINDX8 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> quitDisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsoracle@hpws:/opt/ora12c/oradata/ora12c$ pwd/opt/ora12c/oradata/ora12coracle@hpws:/opt/ora12c/oradata/ora12c$ lltotal 34550864drwxr-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.dbforacle@hpws:/opt/ora12c/oradata/ora12c$ cdoracle@hpws:~$ pwd/home/oracleoracle@hpws:~$ lltotal 58956drwxr-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.bufdrwxr-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 .dmrcdrwxr-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_authdrwxr-xr-x  8 oracle oinstall     4096  6?.25 18:33 .evolution/-rw-r--r--  1 oracle oinstall      179  6?.25 18:12 examples.desktopdrwxr-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.lockdrwx------  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-bookmarksdrwx------  2 oracle oinstall     4096  6?.25 18:33 .gvfs/-rw-------  1 oracle oinstall     4376  7?.31 14:48 .ICEauthoritydrwxr-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_environmentdrwxr-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_historydrwx------  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_historydrwx------  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 .viminfodrwx------  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.olddrwxr-xr-x  2 oracle oinstall     4096  6?.25 18:12 涓.浇/drwxr-xr-x  2 oracle oinstall     4096  6?.25 18:33 妗../oracle@hpws:~$ ls -ltrtotal 372drwxr-xr-x 2 oracle oinstall   4096  6?.25 18:08 backup-rw-r--r-- 1 oracle oinstall    179  6?.25 18:12 examples.desktopdrwxr-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.bufdrwxr-xr-x 2 oracle oinstall   4096  6?.25 18:33 妗..drwxr-xr-x 2 oracle oinstall   4096  6?.25 18:33 monitordrwxr-xr-x 2 oracle oinstall   4096  6?.26 08:04 scriptsdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Videosdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Templatesdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Publicdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Picturesdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Musicdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Downloadsdrwxr-xr-x 2 oracle oinstall   4096  6?.27 16:37 Documents-rw-r----- 1 root   root     302185  6?.27 18:07 erl_crash.dumpdrwxr-xr-x 2 oracle oinstall   4096  7?.11 15:06 Desktop-rw-r--r-- 1 root   root       1566  7?.11 15:08 erlide_debug.txtdrwxr-xr-x 4 oracle oinstall   4096  7?.11 16:42 workspacedrwxr-xr-x 5 oracle oinstall   4096 11?.13 13:48 SYNC_PRO_NEWoracle@hpws:~$ imp userid=cpwebid/cpwebid@ora12c fromuser=cpwebid touser=cpwebid log=user_data.log file=/home/oracle/cpweb.dmpImport: Release 12.1.0.1.0 - Production on Thu Nov 13 15:13:19 2014Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.省略数据库信息IMP-00003: ORACLE error 959 encounteredORA-00959: tablespace TBS_CPWEB does not existImport terminated successfully with warnings.oracle@hpws:~$ imp userid=cpserviceid/cpserviceid@ora11g fromuser=cpserviceid touser=cpserviceid log=user_data.log file=/home/oracle/cpservice.dmpImport: Release 12.1.0.1.0 - Production on Thu Nov 13 15:14:13 2014Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.IMP-00058: ORACLE error 12514 encounteredORA-12514: TNS:listener does not currently know of service requested in connect descriptorIMP-00000: Import terminated unsuccessfullyoracle@hpws:~$ oracle@hpws:~$ imp userid=cpserviceid/cpserviceid@ora12c fromuser=cpserviceid touser=cpserviceid log=user_data.log file=/home/oracle/cpservice.dmpImport: Release 12.1.0.1.0 - Production on Thu Nov 13 15:14:41 2014Copyright (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 ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsExport file created by EXPORT:V11.01.00 via conventional pathimport done in UTF8 character set and AL16UTF16 NCHAR character setimport 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=OPENselect TABLESPACE_NAME from v$tablespace;

 

 

导入导出命令

oracle---导出-----------------------------------------------------------------------------exp a/a@ab file=/home/oracle/a.dmpexp b/b@bb file=/home/oracle/a.dmporacle导入12导入11-----------------------------------------------------------------------下载出来编辑 12 修改为11imp userid=a/a@ab fromuser=a touser=a log=user_data.log file=/home/oracle/a.dmpimp userid=b/b@bb fromuser=b touser=b log=user_data.log file=/home/oracle/b.dmp

 

oracle 在已有实力上创建用户 并导入数据