oracle 导入数据库方法 -20170510

先建立用户：

create user analysis_db identified by analysis_db;
grant resource,connect to analysis_db;

 

SQL>grant sysdba to analysis_db;
SQL>grant imp_full_database to analysis_db;

再导入先建一个URAPPORT_FILE用户
imp userid=analysis_db/analysis_db file=/home/oracle/oracle_data/1/analysis_db.dmp log=/home/oracle/oracle_data/1/analysis_db.log

先登录上10.10.203.124：
[root@ser6-51 ~]# su - oracle
[oracle@ser6-51 ~]$ sqlplus / as sysdba
查询实例：
方法一：
SQL> select name from v$database;

NAME
---------
ORCL

方法二：
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

方法三：
SQL> show parameter instance;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string orcl
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1

SQL> select status from v$instance;

STATUS
------------
OPEN

查询某个用户下的所有表：
使用analysis_db用户登录，然后
[oracle@ser6-51 ~]$ sqlplus analysis_db/analysis_db

SQL> select tname from tab;

 

select * from all_tables where owner = 'analysis_db';

select * from user_tables where table_name = 'ANALYSIS_FOUCE_TOTAL';

select * from user_objects where object_type='TABLE';

 

tips：
oracle的
修改.bash_profile(在sql>下打错字符时，敲退格键时就不会显示^h了）
cd
vi .bash_profile
加入：stty erase ^H

 

drop user pgm cascade;
create user pgm identified by pgmfetion;
grant resource,connect to pgm;
grant execute any procedure to pgm;

alter user pgm default tablespace users;

导入方法：
/u01/app/oracle/product/12c/db_1/bin/imp userid=analysis_db/analysis_db fromuser=analysis_db touser=analysis_db file=/home/oracle/oracle_data/1/analysis_db.dmp log=/home/oracle/oracle_data/1/analysis_db.log
----------------------------------------------------------------------------------------------------------------
报错：
ORA-01950: no privileges on tablespace 'USERS'
修复方法：
SQL> alter user analysis_db quota unlimited on users;

User altered.

--------------
报错：
IMP-00017: following statement failed with ORACLE error 27486:
"BEGIN "
"dbms_scheduler.create_job('"AS_CREATE_DAILY"',"
修复方法：

grant create job to analysis_db;
grant manage scheduler to analysis_db;

 

---------------------------------------------------------------------------------------------------------------
SQL> select grantee,privilege from dba_sys_privs where grantee='RESOURCE' order by privilege;

GRANTEE PRIVILEGE
-------- ----------------------------------------
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE

 

SQL> select grantee,privilege from dba_sys_privs where grantee='CONNECT' order by privilege;

GRANTEE PRIVILEGE
------- ----------------------------------------
CONNECT CREATE SESSION
CONNECT SET CONTAINER

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

先登录上10.10.203.124：
[root@ser6-51 ~]# su - oracle
[oracle@ser6-51 ~]$ sqlplus / as sysdba

SQL> drop user analysis_db cascade;
SQL> create user analysis_db identified by analysis_db;
SQL> grant resource,connect to analysis_db;
SQL> grant imp_full_database to analysis_db;
SQL> grant execute any procedure to analysis_db;
SQL> alter user analysis_db quota unlimited on users;
SQL> grant create job to analysis_db;
SQL> grant manage scheduler to analysis_db;

如果报错：ORA-00959: tablespace 'PUB_MESSAGE_DB' does not exist，表示没有这个表空间，需要创建

创建方法：
create tablespace pub_message_db datafile '/u01/app/oracle/oradata/orcl/pub_message_db.1' size 100m;

SQL> alter user pub_message_db quota 100M on pub_message_db;

导入表方法：
/u01/app/oracle/product/12c/db_1/bin/imp userid=analysis_db/analysis_db fromuser=analysis_db touser=analysis_db file=/home/oracle/oracle_data/1/analysis_db.dmp log=/home/oracle/oracle_data/1/analysis_db.log

1/pub_message_db
1/pub_platform_db
1/system_db
1/urapport_ppmessage
1/urapport_ppnotifications

2/urapport_config
2/urapport_contact
2/urapport_counter
2/urapport
2/urapport_emoticon
2/urapport_message
2/urapport_phonebook

3/cinf_db
3/imop_db
3/navi_db
3/osp_db
3/scfg_db
3/sms_db
3/urapport_group
3/urapport_social

4/urapport_file
4/urapport_main
4/urapport_report
4/urapport_statistical
4/urapport_trace
4/urapport_webmgr_in

 

查询某个用户下的所有表：
使用analysis_db用户登录，然后
[oracle@ser6-51 ~]$ sqlplus analysis_db/analysis_db

SQL> select tname from tab;