首页 > 代码库 > 从头开始db-oracle

从头开始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

 

先备份相关内容,完全,用户,表
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  就不会报错了。

 

从头开始db-oracle