需求:
从102数据库导出库,恢复到用户的服务器,步骤如下:
1、导出数据库
su - oracle
exp census/oracle@orcl BUFFER=64000 FILE=/tmp/centest20180719.DMP OWNER=census
导出报错:
ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
解决:
grant execute on DBMS_JAVA to dba;
2、删除用户
sqlplus / as sysdba
sqlplus census/oracle@1.2.3.4/orcl(带IP的登陆方式)
drop user CENSUS cascade;
(1)如果删除用户报错:
SQL> select status,username,sid,serial# from v$session where USERNAME='CENSUS';
STATUS USERNAME SID SERIAL#
---------- ----------
INACTIVE CENSUS 3634 30640
SQL> alter system kill session'3634,30640';
System altered.
//成功删除session的user后,再继续:
drop user CENSUS cascade;
3、删除表空间
DROP TABLESPACE ECONOMIC_CENSUS INCLUDING CONTENTS AND DATAFILES;
4、创建表空间
#(1)建表空间之前要先看下别的数据都在哪儿放的,看看别的表空间
#查看所有表空间
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
--------------------
/data/ORACLEDATA/oradata/orcl/CENSUS.DMP
#(2)创建表空间
CREATE TABLESPACE ECONOMIC_CENSUS DATAFILE '/data/ORACLEDATA/oradata/orcl/CENSUS.DMP' SIZE 100m autoextend on;
chown -R oracle:oinstall /data/ORACLEDATA
5、创建用户、授权
create user CENSUS identified by oracle default tablespace ECONOMIC_CENSUS;
grant connect,resource, dba,imp_full_database,exp_full_database to census;
6、导入:
imp CENSUS/oracle@1.2.3.4/orcl BUFFER=64000 FILE=/home/oracle/centest20180719.DMP full=y ignore=y
(1)导入的时候报错:
IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_EXPORT_EXTENSION' must be declared
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO census;
再重新导入就好了
7、关于监听
一般这三个文件设置监听:
listener.ora
sqlnet.ora
tnsnames.ora
重启监听、并用系统用户登录
lsnrctl stop
lsnctl start
[oracle@localhost admin]$ sqlplus system/oracle@orcl