存儲過程重建方法

⑴ oracle存儲過程失效重啟後恢復正常

根據oracle資料庫的特點和提供的工具，主要方法有以下幾種方法：

利用邏輯備份使用import工具丟失數據的表

利用物理備份來通過還原數據文件並進行不完全恢復

利用dbms_logmnr包從redo log文件中恢復

利用flashback特性恢復數據

前提

為了方便使用方法的介紹，上述恢復方法都將基於以下場景進行：系統管理員在前一天晚上11點用export對資料庫做了全庫邏輯備份，然後對所有數據文件進行了熱備份。第二天上午10點，系統管理員在修改表TFUNDASSET的數據時，由於修改語句的條件寫錯了，導致一批記錄(幾千條)的ztm欄位被修改成了錯誤的值，而且已經提交。這個表是資產表，相對而言數據變化不頻繁。

一、利用邏輯備份使用import工具恢復丟失的數據

export/import是oracle提供的用於對資料庫進行邏輯備份的工具。該工具適用於備份那些數據量不大、業務量不多的資料庫系統。因為如果在前一天晚上11點用export做了邏輯備份，那麼當今天上午10點資料庫意外崩潰時，從備份起到資料庫崩潰的這段時間里的數據修改操作(包括DDL和DML)都會丟失。如果丟失數據內的表上的數據是相對比較穩定，也就是說該表上基本沒有DML操作，例如標准代碼表、分區表裡的歷史數據，那麼採用import來導入該表可以比較完整的恢復數據。如果該表是經常變化的業務表，那麼這些丟失的數據只能根據業務情況從紙質記錄恢復，或者其他途徑恢復。

▲示例如下：這個表是一個資產表。相對來說，今天系統運行中修改的數據較少，丟失的數據量可以承受或者可以從別的途徑恢復。那就可以用import來恢復。

方法一：

1、把這個表的數據備份到另一個表：

.png

2、刪除該表的記錄:

.png

3、執行下面的命令：

.png

這個命令中在關鍵字tables中指定需要導入的表名字，ignore=y表示忽略表已經存在的錯誤。

4、導入結束後，檢查表中的記錄，並用適當的方法恢復當天的修改。

方法二：

1、 把需要恢復的表導入到另一個用戶下面：

.png

2、檢查數據以後，把原表記錄刪除：

.png

3、然後從另一用戶表中插入回去：

.png

4、 數據量比較大時可以採用如下方法：

.png

二、利用物理備份來通過還原數據文件並進行不完全恢復

如果資料庫運行在歸檔模式下，那麼可以通過使用以前的數據文件備份進行還原，然後利用歸檔日誌進行前滾，直到回滾到錯誤操作的時間點前，然後重置日誌文件打開資料庫。

可以通過下列方法確認是否是運行在歸檔模式：

.png

如果是如上所示，那麼就是運行在歸檔模式了。

▲假定在前一天晚上11點做了全庫物理備份，那麼可以考慮如下恢復：

1、關閉資料庫：

由於資料庫的不完全恢復必須在一個關閉的資料庫上實施，利用一個舊的資料庫的備份還原，然後用日誌根據需要逐步前滾，而不能還原一個新的備份，再回退到某個時間點。

通知各客戶端資料庫將關閉，然後發出：

.png

資料庫已經關閉。

已經卸載資料庫。

ORACLE 常式已經關閉。

2、確定錯誤操作的時間：

可以根據操作員的估計來確定不完全恢復需要前滾停止的時間，也可以利用LogMiner來分析日誌文件(這個工具將在後面介紹)，找出錯誤操作的准確時間。

3、還原數據文件：

先對當前的資料庫文件進行備份，然後再用以前的最近一次備份覆蓋現有數據文件。注意：不覆蓋現有的控制文件。

4、基於時間點恢復，啟動資料庫到裝配狀態：

.png

這樣資料庫就恢復到了2015年10月20日的9點58分零秒。

然後再利用業務資料補充這段時間內的數據。

三、利用dbms_logmnr包從log文件中恢復

這個包是由Oracle提供，與dbms_logmnr_d包配合使用可以方便地分析聯機日誌文件和歸檔日誌文件，從這些日誌文件中提取出所有對資料庫的更改操作。

在使用這個包之前，需要先做一些設置和修改：

1、打開initorcl.ora，修改初始化參數utl_file_dir，設置dbms_logmnr_d包將要使用的數據字典文件的放置目錄。

.png

然後重啟資料庫使參數生效。

2、以sys用戶連接到資料庫執行dbmslmd.sql腳本重建dbms_logmnr_d這個包。

應用Logminer分析重做日誌文件的操作主要有以下步驟：

● 使用dbms_logmnr_d里的存儲過程build創建一個外部數據字典文件；

● 使用dbms_logmnr里的存儲過程add_logfile添加要分析的日誌文件；

● 使用dbms_logmnr里的存儲過程start_logmnr啟動分析；

● 查詢與dbms_logmnr相關的幾個視圖來獲取日誌文件內容；

● 使用dbms_logmnr里的存儲過程end_logmnr結束分析。

▲下面詳細講述使用的過程

1、使用dbms_logmnr_d里的存儲過程build創建一個外部數據字典文件：

.png

2、使用dbms_logmnr里的存儲過程add_logfile添加要分析的日誌文件到待分析文件列表：

.png

如果沒有運行在歸檔模式，那麼由於重做日誌文件的循環使用可能導致日誌文件被覆蓋而無法獲取到所要尋找的恢復條目。如果運行在歸檔模式，則可以通過查看$ORACLE_HOMEadminorclbmp目錄下的alert_orcl.log里日誌文件歸檔的時間和錯誤操作的時間來確定加入哪些歸檔日誌文件到待分析的文件列表中去。

.png

注意：執行以上過程時logfilename參數需要寫日誌文件的全路徑，否則會報錯。重復以上操作直到把所有需要分析的文件都加到列表中去。這樣就可以啟動進行分析。

3、使用dbms_logmnr里的存儲過程start_logmnr啟動分析；

.png

這樣就可以通過下面的查詢來獲取日誌文件的內容了。

4、查詢與dbms_logmnr相關的幾個視圖來獲取日誌文件內容；

.png

這樣就可以找出要恢復所需的語句。注意：v$logmnr_contents只對執行dbms_logmnr.start_logmnr的會話有效，如果通過其他會話或者使用dbms_logmnr.end_logmnr終止了分析，都將不能訪問v$logmnr_contents的數據。如果要使其他會話也能獲取到這些數據，可以通過另外建表來實現，如：

create table undo_sql as select * from v$logmnr_contents。

再對undo_sql進行授權，其他用戶就可以訪問v$logmnr_contents的數據了。

5、使用dbms_logmnr里的存儲過程end_logmnr結束分析。

使用完成以後用下面的命令來結束分析活動：exec dbms_logmnr.end_logmnr;

這樣就釋放了分配給logminer的資源(內存和數據結構)。

從上面的過程可知，如果是更新的數據量比較大，而日誌文件比較小，就可能會導致日誌文件的切換。如果沒有及時去挖掘日誌文件(沒有運行在歸檔模式)，那麼可能會由於日誌文件的循環使用而導致數據不可恢復。如果運行在歸檔模式，也可能由於需要分析的日誌文件比較多而時間較長。

四、利用flashback新特性恢復數據

Oracle9i 開始提供了閃回查詢(Flashback Query)功能，對於誤刪除或者誤更新並且已經commit了的情況提供了簡便快捷的恢復方法；而在Oracle 提供閃回查詢之前，碰到這種情況只能通過備份來進行基於時間點的恢復或者使用logmnr挖掘日誌來恢復，無疑這比閃回查詢要麻煩而且費時。

使用這個Flashback Query特性的前提條件：

1. 資料庫必須處於Automatic Undo Management 狀態。

.png

2. 最大可以閃回查詢的時間段由UNDO_RETENTION 初始化參數(單位為秒)指定

.png

可以通過ALTER SYSTEM SET UNDO_RETENTION = ;來動態修改參數值。

▲如何使用Flashback Query來恢復數據呢？

1. 通過SQL

.png

使用SELECT 語句的AS OF 來進行閃回查詢，語法如下：

使用AS OF 關鍵字來對表，視圖或者物化視圖進行Flashback Query，如果指定了SCN，那麼expr 部分必須是一個數字，如果指定了TIMESTAMP，那麼expr 必須是一個timestamp類型的值。查詢結果將返回在指定的SCN 或者時間點上的數據。

下面我們使用scott 方案來作一個實驗。

.png

如果想在update 的子查詢部分使用AS OF，那麼該查詢只能返回一條記錄，否則將會報錯。

可以通過添加一個臨時表作為中轉，然後再作更新，如下：

.png

2．通過DBMS_FLASHBACK包來恢復

DBMS_FLASHBACK 包提供了以下幾個函數：

ENABLE_AT_TIME：設置當前SESSION 的閃回查詢時間

ENABLE_AT_SYSTEM_CHANGE_NUMBER：設置當前SESSION 的閃回查詢SCN

GET_SYSTEM_CHANGE_NUMBER：取得當前資料庫的SCN

DISABLE：關閉當前SESSION 的閃回查詢

當將一個SESSION 設置為閃回查詢模式之後，後續的查詢都會基於那個時間點或者SCN 的資料庫狀態，如果SESSION 結束，那麼即使沒有明確指定DISABLE，閃回查詢也會自動失效。

當SESSION 運行在閃回查詢狀態時，不允許進行任何DML 和DDL 操作。如果要用DML操作來進行數據恢復就必須使用PL/SQL 游標。

▲示例：

.png

通過上面的例子可以看出，只要這個修改的時間不早於sysdate- (UNDO_RETENTION指定的秒數),就可通過這種方式來恢復數據。

.png

對於問題中的批量數據，可以寫個過程來完成獲取到更改前的數據：

然後再用這個臨時表裡的數據來更新TFUNDASSET就可以了。

五、總結

比較以上幾種恢復數據的方法的使用過程，我們可以看出：

● exp/imp只適合於數據變化不大的表的數據丟失的情況，即使用這種方法處理後也需要從業務辦理資料中修正數據，否則導致數據丟失；

● 採用基於時間點的不完全恢復可以恢復丟失的數據，但是需要關關閉資料庫，減少系統可用時間，而且也會丟失恢復時間點以後的數據；

● 使用LogMiner可以較好的恢復數據，但是要求資料庫盡可能運行在歸檔模式，否則也可能導致數據丟失。好處是不用關閉系統，能夠從日誌文件中得到所有的數據。

● 使用Flashback最方便和簡潔，可以直接得到修改前的數據，但是需要依賴系統設置，並且需要佔用大量的回滾表空間。

因此選擇什麼樣的方法來恢復數據，取決於你的系統環境和具體情況，不能生搬硬套。採用正確的方法才能最大程度的減少數據的丟失。

當然，最好是不需要用到這些恢復的辦法。前提是，你必須做好以下的工作：

1、 為不同環境創建不同的資料庫用戶、不同密碼(如果不能用戶不同，一定要密碼不同)；

2、 將owner和應用用戶分開，並做適度授權；

3、 在做DML前，先用同樣的條件做查詢，看根據結果集是否符合預期。

⑵ sql server中怎樣創建保存數據的存儲過程

在SQL Server中，可以使用兩種方法創建存儲過程 ：
利用SQL Server 管理平台創建存儲過程。
使用Transact-SQL語句中的CREATE PROCEDURE命令創建存儲過程。
創建存儲過程時，需要確定存儲過程的幾個組成部分：
①所有的輸入參數以及傳給調用者的輸出參數。
②被執行的針對資料庫的操作語句，包括調用其它存儲過程的語句。
③返回給調用者的狀態值，以指明調用是成功還是失敗。
④捕獲和處理潛在的錯誤所需的任何錯誤處理語句
定義存儲過程的語法
CREATE PROC[EDURE] 存儲過程名
@參數1 數據類型 = 默認值 OUTPUT,
…… ,
@參數n 數據類型 = 默認值 OUTPUT
AS
SQL語句
GO

⑶ SQL SERVER 如何應用存儲過程呢操作方法有什麼呢

SQL SERVER 如何應用存儲過程呢?

首先最好在SQL SERVER的管理工具中通過create procere寫一條語句來創建存儲過程，創建語句後，點擊工具欄中的執行命令，消息欄中顯示命令已成功完成的消息，證明存儲過程已創建。然後就可以在存儲過程子文件夾下看到自己創建的存儲過程了，執行存儲過程，可以使用exec命令，後跟存儲過程的名稱，另外，還可以在創建存儲過程的時候傳入參數，如下圖，需要使用@符號傳入參數，如果你的存儲過程加了參數，那麼如果你調用的時候沒有傳入參數，SQL SERVER會提示錯誤。

3、存儲過程減少網路流量對於資料庫對象的相同操作，如果將此次操作所涉及的T-SQL語句組織成一個存儲過程，在客戶端調用該存儲過程時，只在網路上傳遞調用語句，否則會是多條 SQL 語句。從而減輕了網路流量，降低了網路負載存儲過程可以用作安全機制，系統管理員可以對要執行的存儲過程的許可權進行限制，從而限制對某些數據的訪問，避免未經授權的用戶訪問數據，保證數據安全。

⑷ Oracle 存儲過程建立問題

alter index alarminfo1 rebuild;

這個得用動態語句執行,改成這樣:
create or replace procere rebuild_index Is
--說 明：重建告警索引,壓縮表空間
begin
execute immediate 'alter index alarminfo1 rebuild';
commit;
end rebuild_index;

⑸ sqlserver 2000 怎麼新建存儲過程

sqlserver 2000 新建存儲過程的方法：
一、這里以「學生庫」為例，為「學生庫」創建一個存儲過程。
①首先啟動企業管理器，打開資料庫，將焦點置於「存儲過程」項上。然後，單擊滑鼠右鍵。
②在快速菜單中，選擇「新建存儲過程」，彈出「存儲過程編輯窗口」。

二、在創建存儲過程中注意下列幾點：
1、不能將 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個批處理中。
2、創建存儲過程的許可權默認屬於資料庫所有者，所有者可將此許可權授予其他用戶。
3、存儲過程是資料庫對象，其名稱必須遵守標識符規則。
4、只能在當前資料庫中創建存儲過程。

⑹ SQL Server 優化存儲過程的方法有哪些

優化存儲過程有很多種方法，下面介紹最常用的7種。
1.使用SET NOCOUNT ON選項
我們使用SELECT語句時，除了返回對應的結果集外，還會返回相應的影響行數。使用SET NOCOUNT ON後，除了數據集就不會返回額外的信息了，減小網路流量。
2.使用確定的Schema
在使用表，存儲過程，函數等等時，最好加上確定的Schema。這樣可以使SQL Server直接找到對應目標，避免去計劃緩存中搜索。而且搜索會導致編譯鎖定，最終影響性能。比如select * from dbo.TestTable比select * from TestTable要好。from TestTable會在當前Schema下搜索，如果沒有，再去dbo下面搜索，影響性能。而且如果你的表是csdn.TestTable的話，那麼select * from TestTable會直接報找不到表的錯誤。所以寫上具體的Schema也是一個好習慣。
3.自定義存儲過程不要以sp_開頭
因為以sp_開頭的存儲過程默認為系統存儲過程，所以首先會去master庫中找，然後在當前資料庫找。建議使用USP_或者其他標識開頭。
4.使用sp_executesql替代exec
原因在Inside Microsoft SQL Server 2005 T-SQL Programming書中的第四章Dynamic SQL裡面有具體描述。這里只是簡單說明一下：sp_executesql可以使用參數化，從而可以重用執行計劃。exec就是純拼SQL語句。
5.少使用游標
可以參考Inside Microsoft SQL Server 2005 T-SQL Programming書中的第三章Cursors裡面有具體描述。總體來說，SQL是個集合語言，對於集合運算具有較高的性能，而Cursors是過程運算。比如對一個100萬行的數據進行查詢，游標需要讀表100萬次，而不使用游標只需要少量幾次讀取。
6.事務越短越好
SQL Server支持並發操作。如果事務過多過長，或是隔離級別過高，都會造成並發操作的阻塞，死鎖。此時現象是查詢極慢，同時cup佔用率極低。
7.使用try-catch來處理錯誤異常
SQL Server 2005及以上版本提供對try-catch的支持，語法為：
begin try
----your code
end try
begin catch
--error dispose
end catch
一般情況可以將try-catch同事務結合在一起使用。
begin try
begin tran
--select
--update
--delete
--…………
commit
end try
begin catch
--if error
rollback
end catch
====================== 分割線 =======================

『自己的一些調優經驗』
1. 少使用游標是個很好的建議，為此，我自己也遇到過一些事故，是游標所造成的，由於，游標是逐行逐行操作的，當記錄較多時，經常會遇到超時的情況。
2. 多表join做查詢時，查詢的欄位盡量不要使用case when then else end的語法，或者使用用戶函數，例如：
select (case when fType=1 then '是' else '否' end) as fTypeName, dbo.F_GetFullName(fID) as fFullName from Table1 inner join Table2……
當兩個表的數據量非常大時，你可以在查詢分析器中明顯感覺到：直接查詢fType和fID與查詢上面兩個欄位的速度，很可能使用了一個case when then就導致超時。
針對這種情況，可以分兩種做法：
第一，把一些簡單的轉換可以放在程序中完成。
第二，如果需要通過ID查詢全名或者全稱，類似的，可以創建好視圖，直接查視圖，或者，先把所有的fFullName查出來放到臨時表中，直接join臨時表（如果這個數據不是很多的話），獲得fFullName。
3. 少使用一些嵌套的查詢，用臨時表緩存中間數據，例如：
select * from Table1
inner join (
select count(1) as count, Table2.ID2 from Table2 inner join Table3 on ID2=ID3 group by Table2.ID2

) as t1 on t1.ID1 = Table1.ID1

我曾經遇到這樣情況，上面的語句是那種情況的簡化版本，把其他不影響結果的表格都去掉了，發現一個奇怪的現象：嵌套查詢的結果集並不大，大約就200多行，Table1有6w條記錄，結果，這個查詢語句超時，查詢分析器中執行2分鍾也得不到結果。
後來，這樣一改，就Ok了，3秒出結果：
select count(1) as count, Table2.ID2 into #temp from Table2 inner join Table3 on ID2=ID3 group by Table2.ID2
select * from Table1
inner join #temp as t1 on t1.ID1 = Table1.ID1
這樣一改，效率提升了幾十倍，猜想：可能是嵌套的查詢是動態的，每一行的join可能都需要先執行嵌套的查詢，從而導致效率極差。
所以，如果查詢足夠復雜，join多個表，需要連接多個通過group by求和、求平均數等運算計算出來的中間數據，那麼，不妨多使用臨時表緩存中間數據。
4. 還有一些是必須遵守的一些默認規則，比如：

先過濾後連接。
查詢的欄位最要不要用「*」，指定需要用的欄位，減少網路流量。

『總結』
對於性能的追求是沒有極限的，做到你所能做到的，這是一個很好的習慣。
有些業務邏輯放在存儲過程中處理比較方便，而有些業務邏輯交給程序來處理，同樣會提升系統整體的效率，看實際情況而定。
總之，盡可能減少這些容易引發性能問題的隱患，系統就會跑得更穩定更有效率，一切從小細節做起。

⑺ 用sqlserver存儲過程重建索引，該怎麼解決

如果語句是你想要的，就把它放到存儲過程裡面
Use [資料庫名稱]
Go
DECLARE @DBCCString NVARCHAR(1000)
DECLARE @TableName VARCHAR(100)
DECLARE Cur_Index CURSOR
FOR
SELECT Name AS TblName
FROM sysobjects
WHERE xType='U'
ORDER BY TblName
FOR READ ONLY
OPEN Cur_Index
FETCH NEXT FROM Cur_Index
INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'
EXEC SP_EXECUTESQL @DBCCString,N'@TblName VARCHAR(100)', @TableName
PRINT '重建表' + @TableName +'的索引........OK!'
FETCH NEXT FROM Cur_Index INTO @TableName
END
CLOSE Cur_Index
DEALLOCATE Cur_Index
PRINT '操作完成！'
go

⑻ 如何修改存儲過程 (SQL Server Management Studio)

兩種方法：1圖形化界面操作，右鍵你要修改的存儲過程，點擊修改
2、SQL語句修改，alert
proc
你要修改的存儲過程

⑼ SQL 存儲過程建立和使用方法

存儲過程沒有想像中的那麼復雜，也就是普通的SQL語句，帶變數的SQL，以及帶參數的存儲過程。LZ的復制粘貼，初學者看得頭大，你看我的就可以了。
如帶變數的存儲過程：
數據表TABLE有YEARMONTH欄位，裡面存儲的是201001，201002這樣的年月
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
CREATE
PROC
dbo.PROC_TESTPROC
AS
DECLARE
@DATE
DATETIME,@YEARMONTH
VARCHAR(6)
SET
@DATE=GETDATE()
SET
@YEARMONTH=CONVERT(VARCHAR(4),YEAR(@DATE))+RIGHT('0'+CONVERT(VARCHAR(2),MONTH(@DATE)),2)
SELECT
*
FROM
TABLE
WHERE
YEARMONTH=@YEARMONTH
GO
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
執行也非常簡單
EXEC
dbo.PROC_TESTPROC
如果有參數，後面跟參數，注意數值及字元型變數
dbo.PROC_TESTPROC
001，'001'