env: Windows Server 2016
SQL Server 2016 SP2
在"SQL Server estimate time create(alter) index phase1"我們利用DMV"sys.dm_exec_query_profiles"查詢create(alter) index執行的即時進度，這次要利用這個方法來查詢一般語法執行的進度。
1.在一般查詢的語法執行前執行以下指令
command:
在單一session啟用STATISTICS PROFILE
例如:
SET STATISTICS PROFILE ON;
另外一個方式是啟用TRACE FLAG 7412
DBCC TRACEON (7412, -1);
GO
2.執行以下語法查詢執行進度
執行以下語法會發現有些步驟完成度超過100%，是實際資料筆數與統計資料的筆數產生落差所致。
command:
USE MASTER
GO
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @SPID INT --= 80;
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @Conditions NVARCHAR(MAX)
SELECT
@SPID=SPID
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command IN ('INSERT')
ORDER BY CPU DESC
SELECT @Conditions =stuff((
SELECT
CAST(physical_operator_name AS NVARCHAR) + ''', N'''
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name
FOR XML PATH('')),1,0,'')
SET @Conditions='(N''' +LEFT(@Conditions, LEN(@Conditions)-4) +')'
--PRINT @Conditions
SET @SQLSTR='SELECT
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, [physical_operator_name], N''<Transition>'')) AS [CurrentStep],
SUM(qp.[estimate_row_count]) AS [TotalRows],
SUM(qp.[row_count]) AS [RowsProcessed],
(SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])) AS [RowsLeft],
CONVERT(DECIMAL(10, 2), ((SUM(qp.[row_count]) * 1.0) / SUM(qp.[estimate_row_count])) * 100) AS [CompletePercent],
CONVERT(varchar, DATEADD(ms, ((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) * 1000, 0), 114) AS [AlreadyRunTime_hhmiss],
CONVERT(varchar, DATEADD(ms, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])))* 1000, 0), 114) AS [RemainTimeToReady_hhmiss],
DATEADD(SECOND, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count]))), GETDATE()) AS [EstimatedCompletionTime]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN ' + @Conditions +'
AND
qp.[session_id] = @SPID'
--PRINT @SQLSTR
EXECUTE SP_EXECUTESQL @SQLSTR, N'@SPID INT',
@SPID=@SPID
--Run this in a different session than the session in which your query is running.
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count)>0, SUM(estimate_row_count), 1) as estimate_percent_complete
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name
ORDER BY node_id desc;
2019年1月5日 星期六
2019年1月1日 星期二
MySQL mysqlbackup backup databse on win2k12r2
env: Windows Server 2012 R2
MySQL 5
Oracle MySQL Enterprise Backup 3.12.3
MySQL維護大多都使用Percona來進行備份與維護，但是Percona只有運行在Linux，因此對於只有Windows環境的企業，藉由"MySQL Enterprise Backup"來進行備份，這次搭配powershell來進行
1.利用powershell備份MySQL
將路徑指到mysqlbackup存放路徑"C:\Program Files\MySQL\MySQL Server 5.5\bin\"
將被分檔存到"F:\DBBackup\MySQL_DBBackup\full\"
command:
cd "C:\Program Files\MySQL\MySQL Server 5.5\bin\"
.\mysqlbackup.exe --host=127.0.0.1 -u dbm -pdbmrpt --compress --backup-dir=F:\DBBackup\MySQL_DBBackup\full\ backup-and-apply-log --with-timestamp
MySQL 5
Oracle MySQL Enterprise Backup 3.12.3
MySQL維護大多都使用Percona來進行備份與維護，但是Percona只有運行在Linux，因此對於只有Windows環境的企業，藉由"MySQL Enterprise Backup"來進行備份，這次搭配powershell來進行
1.利用powershell備份MySQL
將路徑指到mysqlbackup存放路徑"C:\Program Files\MySQL\MySQL Server 5.5\bin\"
將被分檔存到"F:\DBBackup\MySQL_DBBackup\full\"
command:
cd "C:\Program Files\MySQL\MySQL Server 5.5\bin\"
.\mysqlbackup.exe --host=127.0.0.1 -u dbm -pdbmrpt --compress --backup-dir=F:\DBBackup\MySQL_DBBackup\full\ backup-and-apply-log --with-timestamp
2018年12月30日 星期日
SQL Server estimate time create(alter) index phase2
env: Windows Server 2016
SQL Server 2016 SP2
一般在執行SQL command狀況下，我們可以透過動態視圖 "sys.dm_exec_requests"查看進度。
但是建立索引或重建所引時，在"sys.dm_exec_requests"下percent_complete卻呈現"0"。
而組織所引卻可以在"sys.dm_exec_requests"查詢到執行進度。
因此在"SQL Server estimate time create(alter) index phase1"介紹如何檢視create(alter) index進度後，新增可以執行的語句。
1.組織索引並同時查看進度
先利用指令"a"進行組織所引，接著另開新的查詢"sys.dm_exec_requests"
command:
a.ALTER INDEX [PK_test01] ON [dbo].[test01] REORGANIZE WITH ( LOB_COMPACTION = ON )
2.改良"SQL Server estimate time create(alter) index phase1"script內容。
在執行評估中，發現落差大的原因在於'Parallelism','Online Index Insert'這兩項評估筆數造成時間的落差，因此在這次的語法中過濾掉，增加時間準確性。
command:
USE MASTER
GO
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @SPID INT --= 80;
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @Conditions NVARCHAR(MAX)
SELECT
@SPID=SPID
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command IN ('CREATE INDEX', 'ALTER INDEX')
ORDER BY CPU DESC
--Fetch all steps of Create or Alter Index
SELECT @Conditions =stuff((
SELECT
CAST(physical_operator_name AS NVARCHAR) + ''', N'''
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID and physical_operator_name not in('Parallelism','Online Index Insert')
GROUP BY node_id,physical_operator_name
FOR XML PATH('')),1,0,'')
SET @Conditions='(N''' +LEFT(@Conditions, LEN(@Conditions)-4) +')'
--PRINT @Conditions
SET @SQLSTR='SELECT
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, [physical_operator_name], N''<Transition>'')) AS [CurrentStep],
SUM(qp.[estimate_row_count]) AS [TotalRows],
SUM(qp.[row_count]) AS [RowsProcessed],
(SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])) AS [RowsLeft],
CONVERT(DECIMAL(5, 2), ((SUM(qp.[row_count]) * 1.0) / SUM(qp.[estimate_row_count])) * 100) AS [CompletePercent],
CONVERT(varchar, DATEADD(ms, ((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) * 1000, 0), 114) AS [AlreadyRunTime_hhmiss],
CONVERT(varchar, DATEADD(ms, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])))* 1000, 0), 114) AS [RemainTimeToReady_hhmiss],
DATEADD(SECOND, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count]))), GETDATE()) AS [EstimatedCompletionTime]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN ' + @Conditions +'
AND
qp.[session_id] = @SPID'
--PRINT @SQLSTR
EXECUTE SP_EXECUTESQL @SQLSTR, N'@SPID INT',
@SPID=@SPID
--Run this in a different session than the session in which your query is running.
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count)>0, SUM(estimate_row_count), 1) as estimate_percent_complete
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name
ORDER BY node_id;
SQL Server 2016 SP2
一般在執行SQL command狀況下，我們可以透過動態視圖 "sys.dm_exec_requests"查看進度。
但是建立索引或重建所引時，在"sys.dm_exec_requests"下percent_complete卻呈現"0"。
而組織所引卻可以在"sys.dm_exec_requests"查詢到執行進度。
因此在"SQL Server estimate time create(alter) index phase1"介紹如何檢視create(alter) index進度後，新增可以執行的語句。
1.組織索引並同時查看進度
先利用指令"a"進行組織所引，接著另開新的查詢"sys.dm_exec_requests"
command:
a.ALTER INDEX [PK_test01] ON [dbo].[test01] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
b.select percent_complete from sys.dm_exec_requests where command='dbcc'
2.改良"SQL Server estimate time create(alter) index phase1"script內容。
在執行評估中，發現落差大的原因在於'Parallelism','Online Index Insert'這兩項評估筆數造成時間的落差，因此在這次的語法中過濾掉，增加時間準確性。
command:
USE MASTER
GO
SET NOCOUNT ON
SET LOCK_TIMEOUT 10000
DECLARE @SPID INT --= 80;
DECLARE @SQLSTR NVARCHAR(MAX)
DECLARE @Conditions NVARCHAR(MAX)
SELECT
@SPID=SPID
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command IN ('CREATE INDEX', 'ALTER INDEX')
ORDER BY CPU DESC
--Fetch all steps of Create or Alter Index
SELECT @Conditions =stuff((
SELECT
CAST(physical_operator_name AS NVARCHAR) + ''', N'''
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID and physical_operator_name not in('Parallelism','Online Index Insert')
GROUP BY node_id,physical_operator_name
FOR XML PATH('')),1,0,'')
SET @Conditions='(N''' +LEFT(@Conditions, LEN(@Conditions)-4) +')'
--PRINT @Conditions
SET @SQLSTR='SELECT
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0, [physical_operator_name], N''<Transition>'')) AS [CurrentStep],
SUM(qp.[estimate_row_count]) AS [TotalRows],
SUM(qp.[row_count]) AS [RowsProcessed],
(SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])) AS [RowsLeft],
CONVERT(DECIMAL(5, 2), ((SUM(qp.[row_count]) * 1.0) / SUM(qp.[estimate_row_count])) * 100) AS [CompletePercent],
CONVERT(varchar, DATEADD(ms, ((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) * 1000, 0), 114) AS [AlreadyRunTime_hhmiss],
CONVERT(varchar, DATEADD(ms, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count])))* 1000, 0), 114) AS [RemainTimeToReady_hhmiss],
DATEADD(SECOND, ((((MAX(qp.last_active_time) - MIN(qp.first_active_time)) / 1000.0) / SUM(qp.[row_count])) * (SUM(qp.[estimate_row_count]) - SUM(qp.[row_count]))), GETDATE()) AS [EstimatedCompletionTime]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN ' + @Conditions +'
AND
qp.[session_id] = @SPID'
--PRINT @SQLSTR
EXECUTE SP_EXECUTESQL @SQLSTR, N'@SPID INT',
@SPID=@SPID
--Run this in a different session than the session in which your query is running.
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count)>0, SUM(estimate_row_count), 1) as estimate_percent_complete
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name
ORDER BY node_id;
2018年12月29日 星期六
SQL Server estimate time create(alter) index phase1
env: Windows Server 2016
SQL Server 2016 SP2
SQL 2014有推出DMV"sys.dm_exec_query_profiles"查詢執行的即時進度
有一篇問答有明確的script可以參考。
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
用來查詢CREATE(ALTER) INDEX的進度，這對於維護上有一個可預估的時間外，也在施作維護上有一個明確客觀的依據
1.開始使用DMV: sys.dm_exec_query_profiles
在官方文章"sys.dm_exec_query_profiles"有明確說明，執行前必須啟用STATISTICS PROFILE
在單一session啟用STATISTICS PROFILE
例如:
SET STATISTICS PROFILE ON;
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
另外一個方式是啟用TRACE FLAG 7412
DBCC TRACEON - Trace Flags (Transact-SQL):
https://docs.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017
這個使用要注意，因為使用以下command開啟TRACE FLAG 7412，是全域開啟，留意系統資源追蹤即時進度的耗損。
例如:
DBCC TRACEON (7412, -1);
GO
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
2.在第一步啟用STATISTICS PROFILE或TRACE FLAG 7412後，開始利用DMV: sys.dm_exec_query_profiles查詢及實執行進度
下面這一句就是官方文件提供的參考，我有加了IIF避免除以0的錯誤。
SELECT node_id,physical_operator_name, SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count) >0, SUM(estimate_row_count) , 1)
FROM sys.dm_exec_query_profiles
WHERE session_id=54
GROUP BY node_id,physical_operator_name
ORDER BY node_id;
下面是我透過STACKEXCHANGE調整的SCRIPT:
自動抓取"ALTER INDEX"的spid查詢
移除條件:qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort')
這樣會讓時間比較接近實際完成時間。
實際執行結果:
SQL Server 2016 SP2
SQL 2014有推出DMV"sys.dm_exec_query_profiles"查詢執行的即時進度
有一篇問答有明確的script可以參考。
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
用來查詢CREATE(ALTER) INDEX的進度，這對於維護上有一個可預估的時間外，也在施作維護上有一個明確客觀的依據
1.開始使用DMV: sys.dm_exec_query_profiles
在官方文章"sys.dm_exec_query_profiles"有明確說明，執行前必須啟用STATISTICS PROFILE
在單一session啟用STATISTICS PROFILE
例如:
SET STATISTICS PROFILE ON;
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
DBCC TRACEON - Trace Flags (Transact-SQL):
https://docs.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017
這個使用要注意，因為使用以下command開啟TRACE FLAG 7412，是全域開啟，留意系統資源追蹤即時進度的耗損。
例如:
DBCC TRACEON (7412, -1);
GO
ALTER INDEX [IX_TESTIDX01] ON [dbo].[TESTTB01] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
2.在第一步啟用STATISTICS PROFILE或TRACE FLAG 7412後，開始利用DMV: sys.dm_exec_query_profiles查詢及實執行進度
下面這一句就是官方文件提供的參考，我有加了IIF避免除以0的錯誤。
SELECT node_id,physical_operator_name, SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/IIF(SUM(estimate_row_count) >0, SUM(estimate_row_count) , 1)
FROM sys.dm_exec_query_profiles
WHERE session_id=54
GROUP BY node_id,physical_operator_name
ORDER BY node_id;
下面是我透過STACKEXCHANGE調整的SCRIPT:
自動抓取"ALTER INDEX"的spid查詢
移除條件:qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort')
這樣會讓時間比較接近實際完成時間。
USE MASTER
GO
DECLARE @SPID INT --= 80;
SELECT
@SPID=SPID
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE ER.command ='ALTER
INDEX'
ORDER BY CPU DESC
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS
[RowsProcessed],
SUM(qp.[estimate_row_count]) AS
[TotalRows],
MAX(qp.last_active_time) - MIN(qp.first_active_time) AS
[ElapsedMS],
MAX(IIF(qp.[close_time]
= 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N'<Transition>')) AS
[CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE --qp.[physical_operator_name] IN (N'Table Scan',
N'Clustered Index Scan', N'Sort')
--AND
qp.[session_id] = @SPID
), comp AS
(
SELECT *,
([TotalRows]
- [RowsProcessed]) AS [RowsLeft],
([ElapsedMS]
/ 1000.0) AS [ElapsedSeconds]
FROM agg
)
SELECT
[CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed]
* 1.0) / [TotalRows]) * 100) AS [PercentComplete],
[ElapsedSeconds],
(([ElapsedSeconds]
/ [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
DATEADD(SECOND,
(([ElapsedSeconds]
/ [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]
FROM comp
實際執行結果:
2018年12月27日 星期四
Oracle 11g export table to text file
env: Windows Server 2008R2
Oracle 11g
在SQL Server環境中可以透過SSMS把資料庫的table匯出為檔案或是透過sqlcmd來產生文字檔，Oracle環境下常用的工具是Toad.
若要透過指令則是要利用"utl_file"達到匯出為文字檔，並且以逗號做分隔
1.首先我們要建立"utl_file_dir"的directory
command:
create or replace directory utl_file_dir as 'C:\temp';
3.在spfile設定"utl_file_dir"的directory，並重啟Oracle instancet，重啟後再次檢查Oracle parameter "utl_file_dir"設定值
command1:
alter system set utl_file_dir='c:\temp' scope=spfile;
command2:
shutdown immediate
command3:
startup
command4:
select value from v$parameter where name='utl_file_dir';
4.建立測試表格，並顯示測試表格schema內容
command1:
create table dbm.employee_file as select * from hr.employees;
command2:
select count(*) from dbm.employee_file;
command3:
desc dbm.employee_file;
4.執行指令進行匯出文字檔
請留意"v_file_dir varchar2(20)"長度是否比"utl_file_dir"設定的名稱長或相同，否則執會出現錯誤。
Oracle 11g
在SQL Server環境中可以透過SSMS把資料庫的table匯出為檔案或是透過sqlcmd來產生文字檔，Oracle環境下常用的工具是Toad.
若要透過指令則是要利用"utl_file"達到匯出為文字檔，並且以逗號做分隔
1.首先我們要建立"utl_file_dir"的directory
command:
create or replace directory utl_file_dir as 'C:\temp';
SQL> create or replace directory
utl_file_dir as 'C:\temp';
已建立目錄.
2.檢查Oracle parameter "utl_file_dir"設定值
command:
select value from v$parameter where name='utl_file_dir';
SQL> select value from v$parameter where
name='utl_file_dir';
VALUE
--------------------------------------------------------------------------------
command1:
alter system set utl_file_dir='c:\temp' scope=spfile;
command2:
shutdown immediate
command3:
startup
command4:
select value from v$parameter where name='utl_file_dir';
SQL> alter system set utl_file_dir='c:\temp'
scope=spfile;
已更改系統.
SQL> shutdown immediate
資料庫關閉.
資料庫已卸載.
已關閉 ORACLE 執行處理.
SQL> startup
ORACLE 執行處理已啟動.
Total System Global Area 855982080 bytes
Fixed Size 2286032 bytes
Variable Size 520097328 bytes
Database Buffers 327155712 bytes
Redo Buffers 6443008 bytes
資料庫已掛載.
資料庫已開啟.
SQL> select value from v$parameter where
name='utl_file_dir';
VALUE
--------------------------------------------------------------------------------
c:\temp
4.建立測試表格，並顯示測試表格schema內容
command1:
create table dbm.employee_file as select * from hr.employees;
command2:
select count(*) from dbm.employee_file;
command3:
desc dbm.employee_file;
SQL> create table dbm.employee_file as
select * from hr.employees;
已建立表格.
SQL> select count(*) from
dbm.employee_file;
COUNT(*)
----------
107
SQL> desc dbm.employee_file;
名稱
空值? 類型
----------------------------------------
-------- ---------------------------
EMPLOYEE_ID
NUMBER(6)
FIRST_NAME
VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL
VARCHAR2(25)
PHONE_NUMBER
VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL
VARCHAR2(10)
SALARY
NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID
NUMBER(6)
DEPARTMENT_ID NUMBER(4)
請留意"v_file_dir varchar2(20)"長度是否比"utl_file_dir"設定的名稱長或相同，否則執會出現錯誤。
DECLARE
row_result
varchar2(1024);
selectsql
varchar2(1024);
qrycursor
SYS_REFCURSOR;
v_file_dir
varchar2(20);
v_curr_date
varchar2(14);
v_file_title
varchar2(50);
v_file_name
varchar2(37);
txt_handle
UTL_FILE.file_type;
BEGIN
v_file_dir := 'UTL_FILE_DIR';
v_curr_date := to_char(sysdate,
'yyyymmddhh24miss');
v_file_name := 'dba_users' || '_' ||
v_curr_date || '.txt';
selectsql := 'select
OWNER||'',''||OBJECT_NAME||'',''||SUBOBJECT_NAME||'',''||OBJECT_ID||'',''||DATA_OBJECT_ID
from dba_objects ';
txt_handle :=
UTL_FILE.FOPEN(v_file_dir,v_file_name,'w');
open qrycursor for selectsql;
loop
fetch qrycursor into row_result;
exit when qrycursor%notfound;
UTL_FILE.PUT_LINE(txt_handle,row_result);
end loop;
close qrycursor;
UTL_FILE.FCLOSE(txt_handle);
end;
2018年12月25日 星期二
how to stop oracle expdp
env: Oracle Linux 7
Oracle 12c
當我們執行expdp產生檔案過程中，可能因為某些因素需要取消作業，很習慣直接Ctl + c取消指令，但是這可能造成session表面中斷了，但是實際上在背景卻還在執行中。
這會導致重新執行指令會出現錯誤，因此要用標準取消指令的方式進行。
1.可以用指令查詢datapump job執行狀況
command:
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
2.執行expdp指令，並指定table : SYS_EXPORT_TABLE_02，並執行暫停止datapump指令
command1:
expdp dbm/dbm@TESTPDB ATTACH=SYS_EXPORT_TABLE_02
command2:
STOP_JOB=IMMEDIATE
3.找到系統物件SYS_EXPORT_TABLE_02，並且移除
command1:
command2:
drop table DBM.SYS_EXPORT_TABLE_02;
SQL>
Oracle 12c
當我們執行expdp產生檔案過程中，可能因為某些因素需要取消作業，很習慣直接Ctl + c取消指令，但是這可能造成session表面中斷了，但是實際上在背景卻還在執行中。
這會導致重新執行指令會出現錯誤，因此要用標準取消指令的方式進行。
1.可以用指令查詢datapump job執行狀況
command:
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
SQL> SELECT
owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
DBM
SYS_EXPORT_TABLE_01
EXPORT
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
TABLE
EXECUTING
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
DBM
SYS_EXPORT_TABLE_02
EXPORT
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
TABLE
EXECUTING
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
DBM
SYS_EXPORT_TABLE_03
EXPORT
OWNER_NAME
--------------------------------------------------------------------------------
JOB_NAME
--------------------------------------------------------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE
------------------------------
TABLE
EXECUTING
2.執行expdp指令，並指定table : SYS_EXPORT_TABLE_02，並執行暫停止datapump指令
command1:
expdp dbm/dbm@TESTPDB ATTACH=SYS_EXPORT_TABLE_02
command2:
STOP_JOB=IMMEDIATE
[oracle@TEST admin]$ expdp dbm/dbm@TESTPDB ATTACH=SYS_EXPORT_TABLE_02
Export: Release 12.1.0.2.0 - Production on
Sun Oct 15 13:37:28 2017
Copyright (c) 1982, 2015, Oracle and/or its
affiliates. All rights reserved.
Connected to: Oracle Database 12c Standard
Edition Release 12.1.0.2.0 - 64bit Production
Job: SYS_EXPORT_TABLE_02
Owner: DBM
Operation: EXPORT
Creator Privs: TRUE
GUID: 5B8EF07395B21537E0536E52C40A8AE6
Start Time: Sunday, 15 October, 2017 12:01:06
Mode: TABLE
Instance:
Max
Parallelism: 1
Timezone: -07:00
Timezone version: 18
Endianness: LITTLE
NLS
character set: AL32UTF8
NLS
NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter
Value:
CLIENT_COMMAND
dbm/********@TESTPDB tables=SP65647288_DBFS.SHARE CONTENT=DATA_ONLY
directory=soadmp dumpfile=soalob.dmp logfile=soalob.log
INCLUDE_METADATA 0
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job
Error Count: 0
Dump File: /u03/app/oracle/fast_recovery_area/TEST/soalob.dmp
bytes written: 4,096
Worker 1 Status:
Instance ID: 1
Instance name:
Host name:
Process Name: DW01
State: EXECUTING
Object Schema: SP65647288_DBFS
Object Name: SHARE
Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 1
Completed Rows: 3,702,350
Worker Parallelism: 1
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job
([yes]/no): yes
3.找到系統物件SYS_EXPORT_TABLE_02，並且移除
command1:
SELECT o.status, o.object_id, o.object_type,o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
command2:
drop table DBM.SYS_EXPORT_TABLE_02;
SQL> SELECT o.status, o.object_id,
o.object_type,o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND
o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY
4,2; 2
3 4
STATUS
OBJECT_ID OBJECT_TYPE
------- ---------- -----------------------
OWNER.OBJECT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
VALID
105173 TABLE
DBM.SYS_EXPORT_TABLE_02
VALID
105195 TABLE
DBM.SYS_EXPORT_TABLE_03
SQL> drop table DBM.SYS_EXPORT_TABLE_02;
Table dropped.
訂閱：
文章 (Atom)