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;
改自己的密碼,不需要dba的許可權，但要使用舊密碼進行驗證，用以下語句即可
alter user hr identified by 123456 replace zxcasd;


select table_name from user_tables;
desc jobs
set pagesize 200
select * from jobs;
desc locations
set linesize 200
select * from locations;


先備份相關表
分為按使用者導與按表導，
按表的話，需要指定表名。
備份內容有：
一個表中所有的資料
一個表的資料與結構
按使用者的話，該使用者下的所在模式對象都匯出來了

exp help=y
exp hr/123456
回答幾個問題

帶著問題去學習！！！！到這裡所產生的問題
1.exp-00091:exporting questionable statistics.是什麼東西
2.用exp匯出所有的表如何做。上面的需要事Crowdsourced Security Testing道表名，然後一個一個匯出。
經baidu查詢，
問題1解決辦法如下：

指定Linux系統的NLS_LANG環境變數為資料庫的字元集
1）查詢資料庫的字元集（方法很多隻用一種）
SQL> select userenv(‘language‘) from dual;

USERENV(‘LANGUAGE‘)
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

2）設定Linux作業系統的NLS_LANG環境變數
[[email protected] exp]$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
Window系統的環境變數的修改方法是：
C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

問題2解決辦法如下：
具體命令參數可以參考
exp help=y
1、EXP:
      有三種主要的方式（完全、使用者、表）
      1、完全：
          EXP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y
          如果要執行完全匯出，必須具有特殊的許可權
      2、使用者模式：
          EXP hr/123456    BUFFER=64000 FILE=hr.dmp OWNER=hr
          這樣使用者hr的所有對象被輸出到檔案中。
      3、表模式：
          EXP hr/123456    BUFFER=64000 FILE=hr-xue.dmp TABLES=xue
          這樣使用者hr的表xue就被匯出
2、IMP:
      具有三種模式（完全、使用者、表）
      1、完全：
          IMP SYSTEM/123456 BUFFER=64000 FILE=full.dmp FULL=Y
      2、使用者模式：
          IMP hr/123456    BUFFER=64000 FILE=hr.dmp FROMUSER=hr TOUSER=hr
          這樣使用者hr的所有對象被匯入到檔案中。必須指定FROMUSER、TOUSER參數，這樣才能匯入資料。
      3、表模式：
          IMP hr/123456    BUFFER=64000 FILE=hr-xue.dmp TABLES=xue
          這樣使用者hr的表xue就被匯入。有一個問題是，如果表存在，資料不能匯入，只需要在後面加入ignore=y參數就可以了


問題的現象：
EXP-00026: conflicting modes specified
EXP-00000: Export terminated unsuccessfully  

備份檔案會產生 但是沒有內容。
這個問題主要是 exp的時候 裡面參數發生了衝突。
   比較常見的：
1.同時指定了 owner  和 tables
2.同時指定了 FULL 和tables
3.同時指定了多個owner和full
owner 表示匯出整個使用者或者多個使用者  而tables表示只匯出其中的表 。
要麼只匯出其中的表  要麼匯出整個使用者。不然就會衝突了、



增刪改相關行或表，再用備份的資料進行恢複。
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


imp hr/123456
回答幾個問題

帶著問題去學習！！！！到這裡所產生的問題
1.所要回答的問題有些不清楚，需要細查一下。
2.字元集的問題要處理一下。
經baidu查詢，結果如下：



以上的這種方法算是一種邏輯備份，將資料匯入到一個臨時的檔案之中。
還可以物理備份的方法，即將oradata/下面的所有資料全部複製到另外一個安全的地方，也叫冷備
還可以熱備的方法。

ORACLE資料庫有兩類備份方法。第一類為物理備份，該方法實現資料庫的完整恢複，但資料庫必須運行在歸擋模式下（業務資料庫在非歸擋模式下運行），且需要極大的外部存放裝置，例如磁帶庫；第二類備份方式為邏輯備份，業務資料庫採用此種方式，此方法不需要資料庫運行在歸擋模式下，不但備份簡單，而且可以不需要外部存放裝置。
　　
　　資料庫邏輯備份方法
　　
　　ORACLE資料庫的邏輯備份分為三種模式：表備份、使用者備份和完全備份。
　　
　　表模式
　　
　　備份某個使用者模式下指定的對象（表）。業務資料庫通常採用這種備份方式。
　　
　　若備份到本地檔案，使用如下命令：
　　
　　exp icdmain/icd rows=y indexes=n compress=n buffer=65536
　　feedback=100000 volsize=0
　　file=exp_icdmain_csd_yyyymmdd.dmp
　　log=exp_icdmain_csd_yyyymmdd.log
　　tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
　　若直接備份到磁帶裝置，使用如下命令：
　　exp icdmain/icd rows=y indexes=n compress=n buffer=65536
　　feedback=100000 volsize=0
　　file=/dev/rmt0
　　log=exp_icdmain_csd_yyyymmdd.log
　　tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
　　註：在磁碟空間允許的情況下，應先備份到本機伺服器，然後再拷貝到磁帶。出於速度方面的考慮，盡量不要直接備份到磁帶裝置。
　　
　　使用者模式
　　
　　備份某個使用者模式下的所有對象。業務資料庫通常採用這種備份方式。
　　若備份到本地檔案，使用如下命令：
　　exp icdmain/icd owner=icdmain rows=y indexes=n compress=n buffer=65536
　　feedback=100000 volsize=0
　　file=exp_icdmain_yyyymmdd.dmp
　　log=exp_icdmain_yyyymmdd.log
　　若直接備份到磁帶裝置，使用如下命令：
　　exp icdmain/icd owner=icdmain rows=y indexes=n compress=n buffer=65536
　　feedback=100000 volsize=0
　　file=/dev/rmt0
　　log=exp_icdmain_yyyymmdd.log
　　註：如果磁碟有空間，建議備份到磁碟，然後再拷貝到磁帶。如果資料庫資料量較小，可採用這種辦法備份。

    完全模式



　　1、 將資料庫TEST完全匯出，使用者名稱system 密碼manager 匯出到D：\daochu.dmp中
　　exp system/[email protected] file=d：\daochu.dmp full=y
 　 2、 將資料庫中system使用者與sys使用者的表匯出
　　exp system/[email protected] file=d：\daochu.dmp owner=（system，sys）
　　3、 將資料庫中的表table1 、table2匯出
　　exp system/[email protected] file=d：\daochu.dmp tables=（table1，table2）
　　4、 將資料庫中的表table1中的欄位filed1以"00"打頭的資料匯出
　　exp system/[email protected] file=d：\daochu.dmp tables=（table1） query=\" where filed1 like ‘00%‘\"
　　上面是常用的匯出，對於壓縮我不太在意，用winzip把dmp檔案可以很好的壓縮。不過在上面命令後面 加上 compress=y 就可以了



刪除某使用者下的所有對象
Oracle刪除使用者下所有對象的方法未必人人都會，下面就為您介紹兩種常用的Oracle刪除使用者下所有對象的方法，希望對您學習Oracle刪除使用者方面能有所協助。

方法1：
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;


方法2：
寫預存程序實現

DECLARE
TYPE name_list IS TABLE OF VARCHAR2(40);
TYPE type_list IS TABLE OF VARCHAR2(20);

Tab_name name_list:=name_list();
Tab_type type_list:=type_list();

sql_str VARCHAR2(500);
BEGIN
sql_str := ‘select uo.object_name,uo.object_type from user_objects uo where uo.object_type not in(‘‘INDEX‘‘,‘‘LOB‘‘) order by uo.object_type desc‘;
EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;

FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP
sql_str := ‘DROP ‘ || Tab_type(i) || ‘ ‘ || Tab_name(i);
EXECUTE IMMEDIATE sql_str;
END LOOP;
END;

sqlplus hr/123456
drop table jobs cascade constraints;    這樣直接刪除可能會有問題。所以盡量用下面的步驟解決，因為看了以後，沒有明白後續步驟。
drop table jobs
ORA-02449: unique/primary keys in table referenced by foreign keys
報錯，用下面的方法解決

1.檢查哪些表的外鍵引用了要刪除的表的唯一/主鍵.
select A.*
from user_constraints A, user_constraints B
where b.table_name = ‘MYTEST‘
and a.constraint_type = ‘R‘
and a.r_constraint_name = b.constraint_name;

2.產生所有引用要刪除表的外鍵的建立語句.
      select ‘select dbms_metadata.get_ddl(‘‘REF_CONSTRAINT‘‘,‘‘‘ ||
          A.CONSTRAINT_NAME || ‘‘‘) FROM DUAL;‘
       from user_constraints A, user_constraints B
       WHERE b.table_name = ‘JOBS‘
        and a.constraint_type = ‘R‘
        and a.r_constraint_name = b.constraint_name;

select ‘drop table ‘|| table_name ||‘ cascade constraints‘ sql1 from user_tables;
     
3.執行步驟2中產生的SQL語句,擷取外鍵定義.
      set long 3000
      set linesize 3000
      set pagesize 300
      執行步驟2產生的語句.
4.刪除表.
      drop table mytest cascade constraints;
5.刪除並重建表.
6.建立因建立該表所刪除的外鍵約束.
      執行步驟3中擷取到的sql語句.
7.檢查各外鍵是否得到恢複,系統是否正常.

oracle相關問題詳解