从头开始db-oracle

 

 

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