rpm -ivh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm

rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

yum install rlwrap

su - oracle

echo "alias sqlplus='rlwrap sqlplus'" >> ~/.bash_profile

source ~/.bash_profile

sqlplus / as sysdba

show user;

alter user hr identified by zxcasd account unlock;

exit

sqlplus hr/zxcasd

show user;

select * from session_privs;

select * from dba_sys_privs;

select * from dba_tab_privs;

alter user hr identified by 123456 replace zxcasd;

select table_name from user_tables;

set pagesize 200

set linesize 200

desc jobs

select * from jobs;

desc locations

select * from locations;

select userenv('language') from dual;

exit

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

 

session profile

[oracle@allinone ~]$ cat login.sql

set pagesize 200

set linesize 200

define _editor=vi

set time on

set timing on

 

glogin profile

vi /u01/app/oracle/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql

 

 

先备份相关内容，完全，用户，表

exp help=y

exp hr/123456

EXP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y

EXP hr/123456 BUFFER=64000 FILE=hr.dmp OWNER=hr

EXP hr/123456 BUFFER=64000 FILE=hr-xue.dmp TABLES=xue

imp hr/123456

IMP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y

IMP hr/123456 BUFFER=64000 FILE=hr.dmp FROMUSER=hr TOUSER=hr

IMP hr/123456 BUFFER=64000 FILE=hr-xue.dmp TABLES=xue ignore=y

只测试了导出的几种模式，和导入的表模式,用户模式。其它的导入没有成功。

如果表结构存在，imp的时候要加入ignore=y,忽略错误。或者直接删除表数据及表结构，用drop不用delete.

 

增删改相关行或表，再用备份的数据进行恢复。

sqlplus hr/123456

set pagesize 200

set linesize 200

create table xue(id integer,name varchar(25));

insert into xue values(1,'wang');

insert into xue values(2,'liu');

commit;

将表删除

drop table xue;

如果有索引关系的表，用下面

drop table mytest cascade constraints;

留下表结构，删除一行或所有数据。

delete from regions where region_id=5;

delete from regions

 

用户模式恢复

删除用户下所有对象，然后再恢复，以便没有残留。

用sys帐户去操作

drop user hr cascade;

    要退出所有的hr用户连接，才能正确执行。

drop tablespace USERS INCLUDING CONTENTS;

    不能删除默认永久表空间。

create user hr profile default identified by 123456 default tablespace USERS temporary tablespace TEMP account unlock;

grant dba to hr;

grant connect,resource to hr;

imp hr/123456 buffer=64000 file=hr.dmp fromuser=hr touser=hr　　就不会报错了。

 

SQL> select username,account_status from dba_users;

USERNAME               ACCOUNT_STATUS

------------------------------ --------------------------------

SYS                   OPEN

SYSTEM                   OPEN

PLATFORM_USER               OPEN

PLATFORM_MALL               OPEN

LCPSYS                   OPEN

MONITOR                OPEN

OUTLN                   EXPIRED & LOCKED

MGMT_VIEW               EXPIRED & LOCKED