從頭開始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