SQLServer備忘2
●このエラーは、Visual Studio デバッガーがユーザーをデバッグ用データベース サーバーに登録できないときに発生します。 最も可能性の高い原因は、SQL Server のインスタンスに関して必要なアクセス許可がないことです。 sysadmin 固定サーバー ロールを持つログインを使用して Transact-SQL エディターをサーバーに接続するだけでは不十分です。Visual Studio ユーザーの Windows ログインも、SQL Server のそのインスタンス上で sysadmin 固定サーバー ロールのメンバーである必要があります。
https://msdn.microsoft.com/ja-jp/library/vstudio/ms241738(v=vs.100).aspx
https://msdn.microsoft.com/ja-jp/library/vstudio/ms241738(v=vs.100).aspx
●シノニムの生成
create table Owner.b (a int)
create synonym b for Owner.b;
drop synonym b
select * from b;
select * from b
select * from Owner.b
insert into b (a) values (1);
PUBLIC SYNONYMは、権限付与で実現?create synonym b for Owner.b;
drop synonym b
select * from b;
select * from b
select * from Owner.b
insert into b (a) values (1);
●DB内の全テーブルの件数一覧
select distinct a.name, b.row_count
from sys.tables a
left join sys.dm_db_partition_stats b
on a.object_id = b.object_id
order by 2 desc
from sys.tables a
left join sys.dm_db_partition_stats b
on a.object_id = b.object_id
order by 2 desc
●ROWNUMのような値を取得するには。
SELECT column1 FROM
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum,*
FROM logsize) AS t
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum,*
FROM logsize) AS t
●ログの正味量を見る
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
FROM sys.database_files;
●ログの圧縮 (10MBを目標にしたログ圧縮)
DBCC SHRINKFILE(hogehoge_DB_Log,10);
●権限指定
●ストアドの再作成
名前解決時に同じ名前のオブジェクトが存在していれば再作成不要
●SSMSでプロシジャデバッグする設定
(サーバー側)
・TCP ポート 135 を許可
・プログラム sqlservr.exe を許可
※IPSec 経由でネットワーク通信を行う場合、UDP ポート 4500 と UDP ポート 500 も許可する。
・TCP ポート 135 を許可
・プログラム sqlservr.exe を許可
※IPSec 経由でネットワーク通信を行う場合、UDP ポート 4500 と UDP ポート 500 も許可する。
・FWありローカルDBで試行時には、ポートが開いていないと管理者アカウントでないとエラー。
・更にクライアントのWindowsログインユーザーにもsysadmin権限必要。
SSMSを管理者として実行で解消。
・SQL Server ユーザにsysadmin 権限が必要。・更にクライアントのWindowsログインユーザーにもsysadmin権限必要。
●主キーのあるテーブル、ないテーブル、全テーブル数の表示
select count(*) from sys.tables
where object_id IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
where object_id NOT IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
where object_id IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
where object_id NOT IN (select parent_object_id from sys.objects
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
●パブリケーションの設定情報
USE <DB名>
EXEC sp_helppublication @publication = 'パブリケーション名'
GO
EXEC sp_helppublication @publication = 'パブリケーション名'
GO
●アーティクルの設定情報
USE [AdventureWorks2008R2]
EXEC sp_helparticle
@publication = @publication;
GO
EXEC sp_helparticle
@publication = @publication;
GO
●SQLCMDで処理結果件数表示を出力しない
以下を発行する。
set nocount on
set nocount on
●セッションKILL
select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests
sp_who ⇒ これだと利用しているDB名が表示される。
コマンドプロンプトで、
kill プロセスID(セッションID)
select * from sys.dm_exec_requests
sp_who ⇒ これだと利用しているDB名が表示される。
コマンドプロンプトで、
kill プロセスID(セッションID)
--SSMSだけでkillするには--------------------------------------
sp_who
Execute('Kill ' + '78')
--'78'はspidを表す。
--------------------------------------------------------------
sp_who
Execute('Kill ' + '78')
--'78'はspidを表す。
--------------------------------------------------------------
●リンクサーバ
use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin 'DB',false,NULL,'sa','Password'
exec master.dbo.sp_droplinkedsrvlogin 'DB','SERVER\Administrator'
exec master.dbo.sp_dropserver
@server=N'DB'
select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin 'DB',false,NULL,'sa','Password'
exec master.dbo.sp_droplinkedsrvlogin 'DB','SERVER\Administrator'
exec master.dbo.sp_dropserver
@server=N'DB'
select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;
◎以下で設定
use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname ='SERVER\INSTANCE',
@useself='FALSE',@locallogin =NULL,@rmtuser = 'OwnerUser',@rmtpassword = 'OwnerUser'
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname ='SERVER\INSTANCE',
@useself='FALSE',@locallogin =NULL,@rmtuser = 'OwnerUser',@rmtpassword = 'OwnerUser'
◎以下で確認
select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;
●対象DBのみオフラインにし、データファイルを移動した後、オンラインにする。
-- 以下で事前確認
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'OwnerMasterDB');
FROM sys.master_files
WHERE database_id = DB_ID(N'OwnerMasterDB');
use master
alter database OwnerMasterDB set offline;
ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Data, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Mdf' );
ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Log, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Ldf' );
use master
alter database OwnerMasterDB set online;
alter database OwnerMasterDB set offline;
ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Data, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Mdf' );
ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Log, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Ldf' );
use master
alter database OwnerMasterDB set online;
●サーバ名の変更。インスタンス名の変更はできないと思った方がよい。
SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;
sp_dropserver 'WIN-MI2D9GIA9E9';
GO
sp_addserver 'SQLServer', local;
GO
SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;
select * from sys.servers;
sp_dropserver 'WIN-MI2D9GIA9E9';
GO
sp_addserver 'SQLServer', local;
GO
SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;
●レプリケーションで生成した際に裏で自動生成されるリンクサーバの データアクセスのプロパティをTRUEにすると、リンクサーバとして分散クエリが利用できる。
●デフォルト値があるカラムの変更
ALTER TABLE テーブル名 DROP CONSTRAINT DF_xxx_xxx ; --制約削除
ALTER TABLE テーブル名 ALTER COLUMN [フィールド名]INT NOT NULL ; --カラム変更
ALTER TABLE テーブル名 ADD DEFAULT 0 FOR [フィールド名] ; --デフォルト値設定
ALTER TABLE テーブル名 ALTER COLUMN [フィールド名]INT NOT NULL ; --カラム変更
ALTER TABLE テーブル名 ADD DEFAULT 0 FOR [フィールド名] ; --デフォルト値設定
●カラムの追加、変更、削除例
カラムの追加:
カラムの変更:
ALTER TABLE <テーブル名> ADD <カラム名> <データ型> <NULL/NOT NULL> <DEFAULT> …;
例) ALTER TABLE Owner.MASTER ADD TEST_COLUMN NVARCHAR(10) NOT NULL DEFAULT 0;
例) ALTER TABLE Owner.MASTER ADD TEST_COLUMN NVARCHAR(10) NOT NULL DEFAULT 0;
ALTER TABLE <テーブル名> DROP CONSTRAINT <制約名:この場合はDEFAULT>;
ALTER TABLE <テーブル名> ALTER COLUMN <カラム名> <データ型> <NULL/NOT NULL> ;
ALTER TABLE <テーブル名> ADD DEFAULT <デフォルト値> FOR <カラム名>;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___25A5F845;
ALTER TABLE Owner.MASTER ALTER COLUMN TEST_COLUMN CHAR(10) ;
ALTER TABLE Owner.MASTER ADD DEFAULT 0 FOR TEST_COLUMN;
ALTER TABLE <テーブル名> ALTER COLUMN <カラム名> <データ型> <NULL/NOT NULL> ;
ALTER TABLE <テーブル名> ADD DEFAULT <デフォルト値> FOR <カラム名>;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___25A5F845;
ALTER TABLE Owner.MASTER ALTER COLUMN TEST_COLUMN CHAR(10) ;
ALTER TABLE Owner.MASTER ADD DEFAULT 0 FOR TEST_COLUMN;
カラムの削除: 動作に支障がなければ、利用しない方向で調整することを推奨。
●同じパスワード/SID のログインアカウントの作成: sp_help_revlogin
sp_change_users_loginによるログインアカウントの再マッピングは、データベース ユーザーの数が多い場合には、大変な作業になります。そこで、同じパスワード/SIDのログインアカウントを一括で作成できる方法があります。これは、sp_help_revlogin という名前のストアドプロシージャで、マイクロソフトのサポート技術情報(KB:Knowledge Base)の文書番号 918992 で提供されています。
KB918992:ログインとパスワードを SQL Server 2005 のインスタンスの間、転送する方法 この文書は、SQL Server 2005 用ですが、SQL Server 2008 にもそのまま適用可能です。この文書で提供されるスクリプトを丸ごとコピーして、Management Studioのクエリ エディタに貼り付けて実行すれば、sp_help_revlogin ストアドプロシージャを作成することができます。 作成後、次のように実行すれば、同一のパスワード/SIDのログインアカウントを作成するためのスクリプトが生成されます。
USE master EXEC sp_help_revlogin
sp_change_users_loginによるログインアカウントの再マッピングは、データベース ユーザーの数が多い場合には、大変な作業になります。そこで、同じパスワード/SIDのログインアカウントを一括で作成できる方法があります。これは、sp_help_revlogin という名前のストアドプロシージャで、マイクロソフトのサポート技術情報(KB:Knowledge Base)の文書番号 918992 で提供されています。
KB918992:ログインとパスワードを SQL Server 2005 のインスタンスの間、転送する方法 この文書は、SQL Server 2005 用ですが、SQL Server 2008 にもそのまま適用可能です。この文書で提供されるスクリプトを丸ごとコピーして、Management Studioのクエリ エディタに貼り付けて実行すれば、sp_help_revlogin ストアドプロシージャを作成することができます。 作成後、次のように実行すれば、同一のパスワード/SIDのログインアカウントを作成するためのスクリプトが生成されます。
USE master EXEC sp_help_revlogin
●主キー変更例
● オブジェクトの統計情報確認。_WAは列ごとの統計
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;
● 統計の自動更新の設定確認
SELECT name AS "Name",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
● 統計の非同期自動更新の設定確認
SELECT name AS "Name",
is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;
is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;
●オブジェクトごとの統計情報取得
DBCC SHOW_STATISTICS
●Current Worker と Acitive Worker
Current Worker の値から、各スケジューラーに割り当てられている、ワーカースレッド数を確認した。
ワーカースレッドの状態はいくつかあるのだが、今回は Active Worker についてみていく。
ワーカースレッドの状態はいくつかあるのだが、今回は Active Worker についてみていく。
Current Worker はスケジューラーに割り当てられているワーカースレッドの数になるのだが、これは割り当てられている状態を示しており、実際に使われているワーカースレッドということではない。
SELECT
(SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers) AS [CurrentWorker]
, (SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers) AS [Active Worker]
, (SELECT COUNT(*) FROM sys.dm_os_threads) AS [OS Thread (Include Non SQL Server)]
, (SELECT COUNT(*) FROM sys.dm_os_threads
WHERE started_by_sqlservr = 1 AND scheduler_address IS NOT NULL)
AS [OS Thread (Exclude Non SQL Server)]
(SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers) AS [CurrentWorker]
, (SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers) AS [Active Worker]
, (SELECT COUNT(*) FROM sys.dm_os_threads) AS [OS Thread (Include Non SQL Server)]
, (SELECT COUNT(*) FROM sys.dm_os_threads
WHERE started_by_sqlservr = 1 AND scheduler_address IS NOT NULL)
AS [OS Thread (Exclude Non SQL Server)]
●データコレクションはEE?でないと使えない。⇒ウソ。SEでも使える
●オブジェクト権限を確認する
USE データベース名
SELECT
OBJECT_NAME(major_id) AS オブジェクト名
,USER_NAME(grantee_principal_id) AS ユーザー名
,permission_name, state_desc
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('オブジェクト名')
SELECT
OBJECT_NAME(major_id) AS オブジェクト名
,USER_NAME(grantee_principal_id) AS ユーザー名
,permission_name, state_desc
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('オブジェクト名')
●2008ではALTER ROLE、ALTER SERVER ROLEでメンバーを追加できない。
sp_addrolemember、sp_addsrvrolemember を利用する。
●システム定義のメッセージを確認select * from sys.messages
●データコレクタ系ログ
syscollector_collection_items
syscollector_collection_sets
syscollector_collector_types
syscollector_config_store
syscollector_execution_log
syscollector_execution_log_full
syscollector_execution_stats
syscollector_collection_sets
syscollector_collector_types
syscollector_config_store
syscollector_execution_log
syscollector_execution_log_full
syscollector_execution_stats
●データファイルとログファイルの容量
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO
FROM sys.database_files ;
GO
●テーブル毎の容量
-- 領域情報の更新
EXEC sp_spaceused @updateusage = N'TRUE';
GO
--
DECLARE @table sysname
DECLARE @T1 Table (
name sysname
,rows bigint
,reserved varchar(10)
,data varchar(10)
,index_size varchar(10)
,unused varchar(10))
--カーソルの定義
DECLARE C CURSOR LOCAL FOR select s.name+'.'+t.name
from sys.schemas s,sys.tables t
where s.schema_id = t.schema_id
OPEN C
FETCH NEXT FROM C INTO @table --1件読む
WHILE (@@FETCH_STATUS=0)
BEGIN
--テーブル情報のストアド結果を一時テーブルに放り込む
INSERT INTO @T1 EXEC sp_spaceused @table
FETCH NEXT FROM C INTO @table --1件読む
END
CLOSE C
DEALLOCATE C
--結果を表示する
SELECT * FROM @T1
ORDER BY name
EXEC sp_spaceused @updateusage = N'TRUE';
GO
--
DECLARE @table sysname
DECLARE @T1 Table (
name sysname
,rows bigint
,reserved varchar(10)
,data varchar(10)
,index_size varchar(10)
,unused varchar(10))
--カーソルの定義
DECLARE C CURSOR LOCAL FOR select s.name+'.'+t.name
from sys.schemas s,sys.tables t
where s.schema_id = t.schema_id
OPEN C
FETCH NEXT FROM C INTO @table --1件読む
WHILE (@@FETCH_STATUS=0)
BEGIN
--テーブル情報のストアド結果を一時テーブルに放り込む
INSERT INTO @T1 EXEC sp_spaceused @table
FETCH NEXT FROM C INTO @table --1件読む
END
CLOSE C
DEALLOCATE C
--結果を表示する
SELECT * FROM @T1
ORDER BY name
●断片化状況を確認する。
SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DBname'),
NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
FROM sys.dm_db_index_physical_stats (DB_ID(N'DBname'),
NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
●アローケーションユニットごとの情報
select * from sys.allocation_units
sys.dm_exec_sessions v$session?
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats
sys.dm_db_file_space_usage ファイルに関する使用領域(tempDBのみ)
sys.dm_db_session_space_usage セッション毎のページ割り当て(tempDBのみ)
sys.dm_db_partition_stats データベースのパーティションごとに、ページ数と行数の情報
sys.dm_io_pending_io_requests SQL Server で保留中の I/O 要求ごとに 1 行のデータを返します。
sys.dm_io_virtual_file_stats(NULL,NULL) データとログ ファイルの I/O 統計を返します。
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats
sys.dm_db_file_space_usage ファイルに関する使用領域(tempDBのみ)
sys.dm_db_session_space_usage セッション毎のページ割り当て(tempDBのみ)
sys.dm_db_partition_stats データベースのパーティションごとに、ページ数と行数の情報
sys.dm_io_pending_io_requests SQL Server で保留中の I/O 要求ごとに 1 行のデータを返します。
sys.dm_io_virtual_file_stats(NULL,NULL) データとログ ファイルの I/O 統計を返します。
●平均 CPU 時間に基づく上位 5 つのクエリに関する情報を取得する
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
*1 * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
*1 * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
●A. サーバーに接続しているユーザーを検索する
次の例では、サーバーに接続しているユーザーを検索し、各ユーザーのセッション数を返します。
次の例では、サーバーに接続しているユーザーを検索し、各ユーザーのセッション数を返します。
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
FROM sys.dm_exec_sessions
GROUP BY login_name;
●B. 長時間実行されているカーソルを検索する
次の例では、特定の期間よりも長く開いているカーソル、カーソルの作成者、およびカーソルが配置されているセッションを検索。
次の例では、特定の期間よりも長く開いているカーソル、カーソルの作成者、およびカーソルが配置されているセッションを検索。
USE master;
GO
SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;
GO
SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;
●C. トランザクションが開いているアイドル状態のセッションを検索する
次の例では、トランザクションが開いているアイドル状態のセッションを検索します。アイドル状態のセッションとは、現在要求が実行されていないセッションです。
次の例では、トランザクションが開いているアイドル状態のセッションを検索します。アイドル状態のセッションとは、現在要求が実行されていないセッションです。
SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);
SQLServer備忘1
●テーブル情報参照: select * from sys.tables
●ログイン情報の取得: select * from syslogins
●カラム名の取得: select * from syscolumns
●テーブル名の取得: select * from sys.tables
●スキーマ一覧: select * from sys.schemas
●認証を無効にするには: ALTER LOGIN DISABLE
●DBファイル: sys.database_files
●テーブル名の変更: exec sp_rename 'Owner.MASTER', 'MASTER_BAK';
●主キーの変更: exec sp_rename 'Owner.MASTER_PKC','MASTER_PKC_BAK';
●ログイン情報の取得: select * from syslogins
●カラム名の取得: select * from syscolumns
●テーブル名の取得: select * from sys.tables
●スキーマ一覧: select * from sys.schemas
●認証を無効にするには: ALTER LOGIN DISABLE
●DBファイル: sys.database_files
●テーブル名の変更: exec sp_rename 'Owner.MASTER', 'MASTER_BAK';
●主キーの変更: exec sp_rename 'Owner.MASTER_PKC','MASTER_PKC_BAK';
●対象テーブルのカラム名:
●データベース ファイル名の変更方法
●権限チェック
●管理者専用接続(この設定はローカルのみ。リモートを許可する時は1)
https://msdn.microsoft.com/ja-jp/library/ms365941(v=sql.105).aspx
select name from sys.all_columns where object_id =
(select object_id from sys.all_objects where name = '<対象テーブル名>')
order by column_id
●スキーマの生成(select object_id from sys.all_objects where name = '<対象テーブル名>')
order by column_id
●データベース ファイル名の変更方法
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE
( NAME = <論理名>,
FILENAME = 'D:\Database\UserDB\xxxx_201509.mdf' );
※パス名は、変更後のパス名
●SqlConnection.ConnectionString の指定方法例(Windows統合認証の例)ALTER DATABASE <DB名> MODIFY FILE
( NAME = <論理名>,
FILENAME = 'D:\Database\UserDB\xxxx_201509.mdf' );
※パス名は、変更後のパス名
Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;
●dboがデフォルトスキーマになってしまう点。●権限チェック
SQL Server は最初にオブジェクトの所有者と、呼び出し元オブジェクト
対する権限はチェックされません。 あるオブジェクトが、所有者の
異なる別のオブジェクトにアクセスする場合、所有権の継承が途切れるため、
必ず呼び出し元のセキュリティ コンテキストが SQL Server によってチェックされる。
●DBのrename(つまり、チェーンにおける直前のリンク) の所有者とを比較します。
オブジェクトの所有者がどちらも同じであれば、参照されているオブジェクトに対する権限はチェックされません。 あるオブジェクトが、所有者の
異なる別のオブジェクトにアクセスする場合、所有権の継承が途切れるため、
必ず呼び出し元のセキュリティ コンテキストが SQL Server によってチェックされる。
SSMSで可能。ただし、データベース ファイル名はそのままなので、変更が必要。
◎DBリストア時にデータベースファイルを別ファイル名にするには、●管理者専用接続(この設定はローカルのみ。リモートを許可する時は1)
sp_configure 'remote admin connections', 0;
go
reconfigure
go
●設定値確認go
reconfigure
go
exec sp_configure
●ロックエスカレーションの状態確認DBCC tracestatus(1211,-1)
◎SQL Serverを使用しているサービスのパスワード変更時https://msdn.microsoft.com/ja-jp/library/ms365941(v=sql.105).aspx
●データベース ファイル名の変更方法
・不明なユーザー一覧を表示する方法
Shiftキーを押しながら、SSMSを起動する。ログインダイアログボックスが
表示されるので、実行したいログインユーザで認証をする。
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE ( NAME = <論理名>, FILENAME = '<DBファイル名>' );
●リストア後、不明なユーザの対処方法ALTER DATABASE <DB名> MODIFY FILE ( NAME = <論理名>, FILENAME = '<DBファイル名>' );
・不明なユーザー一覧を表示する方法
USE データベース名
EXEC sp_change_users_login 'Report'
・ユーザーマッピングの修復方法
USE データベース名
EXEC sp_change_users_login 'Update_One', 'test', 'test'
(EXEC sp_change_users_login 'Update_One', '現在のDBに存在するユーザー名', 'SQL Serverログイン名')
●別のユーザでWindows認証でログインしたい場合、以下で行う。EXEC sp_change_users_login 'Report'
・ユーザーマッピングの修復方法
USE データベース名
EXEC sp_change_users_login 'Update_One', 'test', 'test'
(EXEC sp_change_users_login 'Update_One', '現在のDBに存在するユーザー名', 'SQL Serverログイン名')
Shiftキーを押しながら、SSMSを起動する。ログインダイアログボックスが
表示されるので、実行したいログインユーザで認証をする。
管理者で起動する場合も、Shiftキーを押しながらSSMSを起動することができる。
●NamedPipeで接続するには
np:サーバー名
SQL Server Management Studio と sqlcmd ユーティリティは、リモートの SQL Server に対して TCP/IP で接続試行し、接続できない場合は名前付きパイプの接続試行に切り替える。
Windows では、TCP/IP での応答の待ち時間 (タイムアウト) は初回 3 秒、再試行回数は 2 回。
再試行時のタイムアウトは前回の倍の秒数となるため、TCP/IP での接続試行により SQL Server に接続できずタイムアウトするまでには、次の合計値の 21 秒要する。
再試行時のタイムアウトは前回の倍の秒数となるため、TCP/IP での接続試行により SQL Server に接続できずタイムアウトするまでには、次の合計値の 21 秒要する。
初回 3 秒 + 1 回目の再試行 6 秒 + 2 回目の再試行 12 秒
以上の動作より、論理的には、SQL Server の待ち受けプロトコルの TCP/IP が無効であり、名前付きパイプが有効である場合は、21 秒より長い接続タイムアウト値でないと SQL Server に接続できない。
●guest
データベース ユーザは、データベース レベルのプリンシパル。
データベース ユーザはすべて、public ロールのメンバ。
guest ユーザデータベースを作成すると、既定で guest ユーザが含まれる。guest ユーザに許可された権限は、このデータベースにユーザ アカウントのないユーザーに継承される。
guest ユーザは削除不可だが、CONNECT 権限を取り消すことで無効にすることは可。CONNECT 権限を取り消すには、master または tempdb 以外のデータベース内で REVOKE CONNECT FROM GUEST を実行。
データベース ユーザはすべて、public ロールのメンバ。
guest ユーザデータベースを作成すると、既定で guest ユーザが含まれる。guest ユーザに許可された権限は、このデータベースにユーザ アカウントのないユーザーに継承される。
guest ユーザは削除不可だが、CONNECT 権限を取り消すことで無効にすることは可。CONNECT 権限を取り消すには、master または tempdb 以外のデータベース内で REVOKE CONNECT FROM GUEST を実行。
●権限等の調査
use master
select * from sys.server_principals
select * from sys.database_principals
◎セッションKILLselect * from sys.server_principals
select * from sys.database_principals
select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests
sp_who ⇒ これだと利用しているDB名が表示される。
kill プロセスID(セッションID)
●◎データベースが (シングル ユーザー) または (制限付きユーザー) のアクセスに設定されている場合select * from sys.dm_exec_requests
sp_who ⇒ これだと利用しているDB名が表示される。
kill プロセスID(セッションID)
[データベース]-[プロパティ]-[オプション]-[その他のオプション]
バックアップを取得すれば切り捨てられる。ログの圧縮で小さくはできる。
[状態] セクション [アクセスの制限] の値を [MULTI_USER] に変更
●ファイルの利用容量?use <DB名>
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
●DBファイルのSHRINKSELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
use <DB名>
SELECT file_id, name
FROM sys.database_files;
DBCC SHRINKFILE (1,TRUNCATEONLY);
1は、file_id
●ログの切り捨てSELECT file_id, name
FROM sys.database_files;
DBCC SHRINKFILE (1,TRUNCATEONLY);
1は、file_id
バックアップを取得すれば切り捨てられる。ログの圧縮で小さくはできる。
・ログを最後にバックアップしてからチェックポイントが発生したこと。
チェックポイントの後、少なくとも次のトランザクション ログのバックアップまで
ログはそのまま保たれる。
・ログ トランザクションを妨げる要因が他にないこと。ログはそのまま保たれる。
(長時間の処理や、レプリケーションが未完等)
・BACKUP LOG ステートメントで WITH COPY_ONLY を指定していないこと。
●ログの空き容量
use <DB名>
DBCC SQLPERF(LOGSPACE);
GO
●ログの切り捨てが遅れている理由DBCC SQLPERF(LOGSPACE);
GO
use <DB名>
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases
●メンテナンスプラン の結果ログ出力先select name,log_reuse_wait,log_reuse_wait_desc from sys.databases
メンテナンスプランの[接続の管理…]コンボボックスの右隣に
「レポートとログ記録」というアイコンがあり、そこで出力先を指定する」
「レポートとログ記録」というアイコンがあり、そこで出力先を指定する」
●getdate()の使い方例(適当なテーブルを指定しても出力できる)
●DatabaseMailUserRole ロールにユーザーを追加するには、
select getdate(),* from sys.tables
select getdate()-10
●各プリンシパルへの権限付与状況select getdate()-10
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.server_permissions as e
join sys.server_principals as p
on e.grantee_principal_id = p.principal_id
●各データベースユーザへの権限付与状況from sys.server_permissions as e
join sys.server_principals as p
on e.grantee_principal_id = p.principal_id
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.database_permissions as e
join sys.database_principals as p
on e.grantee_principal_id = p.principal_id
●DROP INDEX文の作成from sys.database_permissions as e
join sys.database_principals as p
on e.grantee_principal_id = p.principal_id
●DatabaseMailUserRole ロールにユーザーを追加するには、
事前に、対象ユーザをmsdbのDatabaseMailUserRole ロールにユーザマッピングし、当該ロールを付与しておく。
use msdb
exec sp_addrolemember DatabaseMailUserRole, <ユーザ名>
セキュリティを向上させるために、データベース メールでメール プロファイルへのアクセスが管理される。プロファイルはパブリックまたはプライベートにできる。msdb データベースの DatabaseMailUserRole データベース ロールのすべてのメンバが、パブリック プロファイルを利用できます。exec sp_addrolemember DatabaseMailUserRole, <ユーザ名>
DatabaseMailUserRole ロールのすべてのメンバは、このプロファイルを使用して電子メールを送信可。プライベート プロファイルは、msdb データベースのセキュリティ プリンシパル用に定義されます。指定したデータベース ユーザー、ロール、および sysadmin 固定サーバー ロールのメンバのみ、このプロファイルを使用して電子メールを送信できます。既定では、プロファイルはプライベートで、sysadmin 固定サーバー ロールのメンバのみがアクセスできます。プライベート プロファイルを使用するには、sysadmin が、プライベート プロファイルを使用するための権限をユーザーに与える必要があります。
また、sp_send_dbmail ストアド プロシージャの EXECUTE 権限は、DatabaseMailUserRole のメンバにのみ与えられます。ユーザーが電子メール メッセージを送信できるようにするには、システム管理者がユーザーを DatabaseMailUserRole データベース ロールに追加する必要があります。
●データベースメール設定を確認する設定
●DatabaseMailUserRoleロールメンバーを確認するには
プリンシパルは、SQL Server リソースを要求できるエンティティです。SQL Server の承認モデルの他のコンポーネントと同様に、プリンシパルは階層内に配置できます。プリンシパルの効力のスコープは、プリンシパルの定義のスコープ (Windows、サーバー、データベース) と、プリンシパルが分割できないアイテムであるかコレクションであるかによって異なります。分割できないプリンシパルの例には Windows ログインがあり、コレクションであるプリンシパルの例には Windows グループがあります。各プリンシパルには、1 つのセキュリティ識別子 (SID) があります。
◆ Windows レベルのプリンシパル
- Windows ドメイン ログイン
- Windows ローカル ログイン
◆SQL Server レベルのプリンシパル
- SQL Server ログイン
◆データベースレベルのプリンシパル
- データベース ユーザー
- データベース ロール
- アプリケーション ロール
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
●データベースメールを設定するにはGO
RECONFIGURE;
GO
sp_configure;
GO
●DatabaseMailUserRoleロールメンバーを確認するには
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
●データベース メールを送信するユーザーは、少なくとも 1 つのデータベース メール プロファイルにアクセスできる必要があります。ユーザー (プリンシパル) とそのユーザーがアクセスできるプロファイルを一覧するには、次のステートメントを実行します。EXEC msdb.dbo.sysmail_help_principalprofile_sp;
●プリンシパルプリンシパルは、SQL Server リソースを要求できるエンティティです。SQL Server の承認モデルの他のコンポーネントと同様に、プリンシパルは階層内に配置できます。プリンシパルの効力のスコープは、プリンシパルの定義のスコープ (Windows、サーバー、データベース) と、プリンシパルが分割できないアイテムであるかコレクションであるかによって異なります。分割できないプリンシパルの例には Windows ログインがあり、コレクションであるプリンシパルの例には Windows グループがあります。各プリンシパルには、1 つのセキュリティ識別子 (SID) があります。
◆ Windows レベルのプリンシパル
- Windows ドメイン ログイン
- Windows ローカル ログイン
◆SQL Server レベルのプリンシパル
- SQL Server ログイン
◆データベースレベルのプリンシパル
- データベース ユーザー
- データベース ロール
- アプリケーション ロール
◆SQL Server sa ログイン
SQL Server sa ログインは、サーバー レベルのプリンシパルです。このログインは、インスタンスのインストール時に既定で作成されます。SQL Server 2005 および SQL Server 2008 では、sa の既定のデータベースは master です。これは、以前のバージョンの SQL Server の動作から変更されています。
SQL Server sa ログインは、サーバー レベルのプリンシパルです。このログインは、インスタンスのインストール時に既定で作成されます。SQL Server 2005 および SQL Server 2008 では、sa の既定のデータベースは master です。これは、以前のバージョンの SQL Server の動作から変更されています。
◆public データベース ロール
データベース ユーザーはすべて、public データベース ロールに属しています。セキュリティ保護可能なリソースに対する特定の権限が与えられていないか権限が拒否されたユーザーは、public がそのリソースに対して許可されている権限を継承します。
データベース ユーザーはすべて、public データベース ロールに属しています。セキュリティ保護可能なリソースに対する特定の権限が与えられていないか権限が拒否されたユーザーは、public がそのリソースに対して許可されている権限を継承します。
◆INFORMATION_SCHEMA と sys
各データベースには、カタログ ビューにユーザーとして表示される 2 つのエンティティ INFORMATION_SCHEMA および sys が含まれています。SQL Server はこれらを必要とします。これらのエンティティはプリンシパルではなく、変更も削除もできません。
各データベースには、カタログ ビューにユーザーとして表示される 2 つのエンティティ INFORMATION_SCHEMA および sys が含まれています。SQL Server はこれらを必要とします。これらのエンティティはプリンシパルではなく、変更も削除もできません。
◆証明書ベースの SQL Server ログイン
名前が 2 つの番号記号 (##) で囲まれたサーバー プリンシパルは、内部システムでのみ使用されます。SQL Server のインストール時に証明書から作成される以下のプリンシパルは、削除しないでください。
名前が 2 つの番号記号 (##) で囲まれたサーバー プリンシパルは、内部システムでのみ使用されます。SQL Server のインストール時に証明書から作成される以下のプリンシパルは、削除しないでください。
●メールを飛ばす場合(SQL Server Agentからの場合)
EXECUTE msdb.dbo.sp_notify_operator @name=N'operator1',@subject=N'【タイトル】DBバックアップ処理',@body=N'表記処理正常終了'
●権限系メモuse <DB名>
select * from sys.objects
SELECT * FROM fn_builtin_permissions(default);
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('SCHEMA') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('LOGIN') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('USER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('OBJECT') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('ENDPOINT') ORDER BY permission_name;
●ロールに含まれる権限を調べるには。select * from sys.objects
SELECT * FROM fn_builtin_permissions(default);
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('SCHEMA') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('LOGIN') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('USER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('OBJECT') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('ENDPOINT') ORDER BY permission_name;
sp_dbfixedrolepermission で取得
●CONNECT BYの代替サンプル
のE.参照。
●包含権限を確認する。(ORACLEのCONNECT BY、LEVEL擬似列の代替サンプル)
スコープは以下'SERVER'を変える。
WITH n(permission_name, covering_permission_name, level) AS
(SELECT permission_name, covering_permission_name , 0 as [LEVEL]
FROM sys.fn_builtin_permissions('')
WHERE class_desc = 'SERVER'
and len(covering_permission_name) = 0
UNION ALL
SELECT nplus1.permission_name, nplus1.covering_permission_name , [LEVEL] + 1
FROM sys.fn_builtin_permissions('') as nplus1, n
WHERE n.permission_name = nplus1.covering_permission_name
and nplus1.class_desc = 'SERVER')
SELECT RIGHT(' '+permission_name,len(permission_name)+level),covering_permission_name,level FROM n
上記スコープ('SERVER'の部分)の候補:
XML SCHEMA COLLECTION
TYPE
ASYMMETRIC KEY
APPLICATION ROLE
LOGIN
REMOTE SERVICE BINDING
CONTRACT
MESSAGE TYPE
ROUTE
OBJECT
ASSEMBLY
USER
ENDPOINT
SERVER
DATABASE
SCHEMA
SYMMETRIC KEY
CERTIFICATE
FULLTEXT STOPLIST
ROLE
FULLTEXT CATALOG
SERVICE
●SQL Server カラム定義を変更すると「変更の保存が許可されていません」が表示された場合の対処法WITH n(permission_name, covering_permission_name, level) AS
(SELECT permission_name, covering_permission_name , 0 as [LEVEL]
FROM sys.fn_builtin_permissions('')
WHERE class_desc = 'SERVER'
and len(covering_permission_name) = 0
UNION ALL
SELECT nplus1.permission_name, nplus1.covering_permission_name , [LEVEL] + 1
FROM sys.fn_builtin_permissions('') as nplus1, n
WHERE n.permission_name = nplus1.covering_permission_name
and nplus1.class_desc = 'SERVER')
SELECT RIGHT(' '+permission_name,len(permission_name)+level),covering_permission_name,level FROM n
上記スコープ('SERVER'の部分)の候補:
XML SCHEMA COLLECTION
TYPE
ASYMMETRIC KEY
APPLICATION ROLE
LOGIN
REMOTE SERVICE BINDING
CONTRACT
MESSAGE TYPE
ROUTE
OBJECT
ASSEMBLY
USER
ENDPOINT
SERVER
DATABASE
SCHEMA
SYMMETRIC KEY
CERTIFICATE
FULLTEXT STOPLIST
ROLE
FULLTEXT CATALOG
SERVICE
基本はテーブル再作成が必要な場合、警告される模様。
http://nasunoblog.blogspot.jp/2013/10/sql-server-column-edit-error.html
http://nasunoblog.blogspot.jp/2013/10/sql-server-column-edit-error.html
●columnからSQL生成
以下は2つのINSERT-SELECTを作成する。
https://msdn.microsoft.com/ja-jp/library/ms140052(v=sql.105).aspx
https://technet.microsoft.com/ja-jp/library/ms175937%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
http://mtgsqlserver.blogspot.jp/2013/03/blog-post_10.html#id26
use <DB名>
select SQL FROM (
select 2 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + '<テーブル名1>' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='<テーブル名1>'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='<テーブル名1>')
UNION ALL
select 2 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名1'))
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名1')
UNION ALL
select 3 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
UNION ALL
select 3 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
) AS A order by A,B,column_id
●WITH句の利用例(入力パラメータを設定することはできない模様で、テーブル名2は正しく出力できない)select SQL FROM (
select 2 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + '<テーブル名1>' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='<テーブル名1>'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='<テーブル名1>')
UNION ALL
select 2 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名1'))
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名1')
UNION ALL
select 3 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
UNION ALL
select 3 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
) AS A order by A,B,column_id
use <DB名>;
WITH TabName(oid) AS
(select object_id as oid from sys.tables where name ='テーブル名1')
,MaxTab(m) AS
(select max(column_id) as m from sys.columns
where object_id = (select oid from TabName))
select SQL FROM (
select 2 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名1' + ' ( ' + name
when (select m from MaxTab)
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id = (select oid from TabName)
UNION ALL
select 2 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name when (select m from MaxTab)
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id = (select oid from TabName)
UNION ALL
select 3 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
UNION ALL
select 3 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id)from sys.columns where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
) AS A order by A,B,column_id
●エクスポート・インポートWITH TabName(oid) AS
(select object_id as oid from sys.tables where name ='テーブル名1')
,MaxTab(m) AS
(select max(column_id) as m from sys.columns
where object_id = (select oid from TabName))
select SQL FROM (
select 2 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名1' + ' ( ' + name
when (select m from MaxTab)
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id = (select oid from TabName)
UNION ALL
select 2 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name when (select m from MaxTab)
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id = (select oid from TabName)
UNION ALL
select 3 as A,0 as B,
case column_id
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name
when (select max(column_id) from sys.columns
where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' ) '
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
UNION ALL
select 3 as A,1 as B,
case column_id
when 1 THEN 'SELECT ' + name
when (select max(column_id)from sys.columns where object_id = (select object_id from sys.tables
where name ='テーブル名2'))
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';'
else ',' + name
end AS SQL
,column_id
from sys.columns where object_id =
(select object_id from sys.tables where name ='テーブル名2')
) AS A order by A,B,column_id
https://msdn.microsoft.com/ja-jp/library/ms140052(v=sql.105).aspx
https://technet.microsoft.com/ja-jp/library/ms175937%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
http://mtgsqlserver.blogspot.jp/2013/03/blog-post_10.html#id26
エクスポート
https://technet.microsoft.com/ja-jp/library/ms175915(v=sql.105).aspx
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -T -c
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
インポートbcp AdventureWorks2008R2.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c
空テーブルの作成USE AdventureWorks2008R2;
GO
SELECT * INTO AdventureWorks2008R2.Sales.Currency2
FROM AdventureWorks2008R2.Sales.Currency WHERE 1=2
BULK INSERTの例GO
SELECT * INTO AdventureWorks2008R2.Sales.Currency2
FROM AdventureWorks2008R2.Sales.Currency WHERE 1=2
https://technet.microsoft.com/ja-jp/library/ms175915(v=sql.105).aspx
●IDENTITY列があるテーブルへのインポート例(INSERT文)
https://msdn.microsoft.com/ja-jp/library/ms188059.aspx?f=255&MSPPError=-2147217396
https://msdn.microsoft.com/ja-jp/library/ms188059.aspx?f=255&MSPPError=-2147217396
SET IDENTITY_INSERT SAMPLE_1 ON
INSERT INTO SAMPLE_1
(ID,
Name,
Status,
XTime)
SELECT ID,
Name,
Status,
XTime
FROM SAMPLE_2
SET IDENTITY_INSERT SAMPLE_1 OFF
●テーブル事前確認(どのテーブルがどのスキーマか)INSERT INTO SAMPLE_1
(ID,
Name,
Status,
XTime)
SELECT ID,
Name,
Status,
XTime
FROM SAMPLE_2
SET IDENTITY_INSERT SAMPLE_1 OFF
use <DB>
select t.name,s.name,s.schema_id from sys.tables t,sys.schemas s
where t.schema_id = s.schema_id;
●リコンパイルするには。select t.name,s.name,s.schema_id from sys.tables t,sys.schemas s
where t.schema_id = s.schema_id;
sp_recompile
b) 次回実行時にリコンパイルする(テーブル or ストアドプロシージャ単位) sp_recompile
特定のクエリが遅い場合の試行方法
使用例) tbl_01 テーブルを対象とするストアド プロシージャおよびトリガーが次回実行時に再コンパイルされます。
https://support.microsoft.com/ja-jp/kb/243586
USE mydatabase;
GO
EXEC sp_recompile N'dbo.tbl_01';
GO
●ストアドプロシジャ一覧GO
EXEC sp_recompile N'dbo.tbl_01';
GO
SELECT name, crdate FROM sysobjects WHERE type='P' ORDER BY name;
●ストアドリコンパイルのトラブルシューティングhttps://support.microsoft.com/ja-jp/kb/243586
●カウントを取ってみる(テーブル名,件数 で表示したい場合)。
use <DB名>
select 'テーブル名1' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名1 UNION ALL
select 'テーブル名2' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名2 UNION ALL
select 'テーブル名3' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名3 ;
●スキーマ権限で、あるスキーマ全体を見えなくできるのではselect 'テーブル名1' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名1 UNION ALL
select 'テーブル名2' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名2 UNION ALL
select 'テーブル名3' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名3 ;
結局、双方のレプリカサイトで同じユーザ/パスワードのアカウントが必要
●MINUSのかわりはEXCEPT
select * from [DB].[SCHEMA].テーブル名1
except
select * from [DB].[SCHEMA].テーブル名2
●useの説明except
select * from [DB].[SCHEMA].テーブル名2
SQL Server ログインで SQL Server に接続すると、自動的に既定のデータベースに接続し、データベース ユーザーのセキュリティ コンテキストを取得できます。SQL Server ログイン用のデータベース ユーザーが 1 人も作成されていない場合、ログインは guest として接続されます。データベース ユーザーが、データベースに対する CONNECT 権限を持たない場合、USE ステートメントは失敗します。ログインに既定のデータベースが割り当てられていない場合、既定のデータベースとして master が設定されます。
USE は、コンパイル時と実行時の両方で実行でき、その効果は直ちに有効になります。したがって、バッチ内で USE ステートメントの後にあるステートメントは、指定したデータベースで実行されます。
USE は、コンパイル時と実行時の両方で実行でき、その効果は直ちに有効になります。したがって、バッチ内で USE ステートメントの後にあるステートメントは、指定したデータベースで実行されます。
●goの説明
GO は Transact-SQL ステートメントではなく、sqlcmd および osql ユーティリティと SQL Server Management Studio コード エディターで認識されるコマンドです。
SQL Server のユーティリティでは、GO は、現在の Transact-SQL ステートメントのバッチを SQL Server インスタンスに送信するためのシグナルとして解釈されます。現在のステートメントのバッチは、前回の GO の後に入力されたすべてのステートメントで構成されます。最初の GO の場合、現在のバッチは、アドホック セッションまたはスクリプトの開始後に入力されたすべてのステートメントで構成されます。
Transact-SQL ステートメントを GO コマンドと同じ行に入力することはできません。ただし、GO コマンドの行にコメントは入力できます。
ユーザーは、バッチの規則に従う必要があります。たとえば、バッチの最初のステートメント以降でストアド プロシージャを実行する場合は、バッチに EXECUTE キーワードを含める必要があります。ローカル (ユーザー定義) 変数のスコープはバッチ内に限られ、GO コマンドの後では参照できません。
GO は Transact-SQL ステートメントではなく、sqlcmd および osql ユーティリティと SQL Server Management Studio コード エディターで認識されるコマンドです。
SQL Server のユーティリティでは、GO は、現在の Transact-SQL ステートメントのバッチを SQL Server インスタンスに送信するためのシグナルとして解釈されます。現在のステートメントのバッチは、前回の GO の後に入力されたすべてのステートメントで構成されます。最初の GO の場合、現在のバッチは、アドホック セッションまたはスクリプトの開始後に入力されたすべてのステートメントで構成されます。
Transact-SQL ステートメントを GO コマンドと同じ行に入力することはできません。ただし、GO コマンドの行にコメントは入力できます。
ユーザーは、バッチの規則に従う必要があります。たとえば、バッチの最初のステートメント以降でストアド プロシージャを実行する場合は、バッチに EXECUTE キーワードを含める必要があります。ローカル (ユーザー定義) 変数のスコープはバッチ内に限られ、GO コマンドの後では参照できません。
SQL Server アプリケーションでは、複数の Transact-SQL ステートメントを SQL Server インスタンスに送信し、バッチとして実行できます。バッチ内のステートメントは、1 つの実行プランにコンパイルされます。SQL Server のユーティリティでアドホック ステートメントを実行する場合、または SQL Server ユーティリティを介して実行する Transact-SQL ステートメントのスクリプトを作成する場合、プログラマは GO を使用してバッチの終了を知らせる必要があります。
ODBC または OLE DB API に基づくアプリケーションで、GO コマンドを実行しようとすると、構文エラーになります。SQL Server のユーティリティからサーバーに GO コマンドが送信されることはありません。
ODBC または OLE DB API に基づくアプリケーションで、GO コマンドを実行しようとすると、構文エラーになります。SQL Server のユーティリティからサーバーに GO コマンドが送信されることはありません。
GO は、権限を必要としないユーティリティ コマンドです。すべてのユーザーが実行できます。
---------------------------------------------------------------------
RACめも
/etc/hosts変更:
192.168.20.51 r1.12c.jp r1
192.168.20.61 r1-vip.12c.jp r1-vip
192.168.20.52 r2.12c.jp r2
192.168.20.62 r2-vip.12c.jp r2-vip
192.168.20.53 r3.12c.jp r3
192.168.20.63 r3-vip.12c.jp r3-vip
192.168.20.200 rac.12c.jp rac
192.168.20.201 rac.12c.jp rac
192.168.20.202 rac.12c.jp rac
192.168.20.51 r1.12c.jp r1
192.168.20.61 r1-vip.12c.jp r1-vip
192.168.20.52 r2.12c.jp r2
192.168.20.62 r2-vip.12c.jp r2-vip
192.168.20.53 r3.12c.jp r3
192.168.20.63 r3-vip.12c.jp r3-vip
192.168.20.200 rac.12c.jp rac
192.168.20.201 rac.12c.jp rac
192.168.20.202 rac.12c.jp rac
# systemctl start dnsmasq
# systemctl enable dnsmas
# systemctl enable dnsmas
■SELINUXを切
/etc/selinux/config
disabledにする。
■不要なNetwork Interfaceを削除
# virsh net-list --all
ネットワーク default は強制停止されました
名前 状態 自動起動 永続
----------------------------------------------------------default 動作中 はい (yes) はい (yes)
# virsh net-destroy defaultネットワーク default は強制停止されました
# virsh net-autostart default --disable
ネットワーク default の自動起動設定が解除されました
ネットワーク default の自動起動設定が解除されました
# virsh net-list --all
■ユーザ、グループ、ディレクトリの作成
groupadd oinstall
groupadd dba
groupadd asmadmin
groupadd asmdba
groupadd asmoper
useradd -u 500 -g oinstall -G dba,asmadmin,asmdba -d /home/oracle oracle
useradd -u 501 -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid grid
# mkdir -p /u01/app/grid
# mkdir -p /u01/app/12.1.0/grid
mkdir -p /u01/app/12.1.0
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle
# mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
mkdir -p /u01/app/oracle/product/12.1.0
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01
passwd oracle
passwd grid
groupadd dba
groupadd asmadmin
groupadd asmdba
groupadd asmoper
useradd -u 500 -g oinstall -G dba,asmadmin,asmdba -d /home/oracle oracle
useradd -u 501 -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid grid
# mkdir -p /u01/app/grid
# mkdir -p /u01/app/12.1.0/grid
mkdir -p /u01/app/12.1.0
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle
# mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
mkdir -p /u01/app/oracle/product/12.1.0
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01
passwd oracle
passwd grid
■Firewallの停止
systemctl stop firewalld
systemctl disable firewalld
systemctl disable firewalld
■avahi-daemonサービスを停止する。
■ntpの設定があれば削除
/etc/ntp.conf というようなファイルがあればrename(ファイル名は曖昧)
■リソース制限変更
# vi /etc/security/limits.conf
<以下を追記>
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock unlimited
oracle hard memlock unlimited
<以下を追記>
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock unlimited
oracle hard memlock unlimited
/etc/sysctl.confに以下を追加
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
■grid、oracleのログインシェルに、以下を追加
ulimit -u 16384 -n 65536
■zeroconf の確認
echo "NOZEROCONF=yes" >> /etc/sysconfig/network
■ASM用のRAWパーティションを作成する。
------------------------------------------------------------
fdiskでパーティション作成
----------------------------------------------------------
vmxファイルに以下を追加
disk.locking = "false"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0"
diskLib.dataCacheMinReadAheadSize = "0"
diskLib.dataCachePageSize = "4096"
diskLib.maxUnsyncedWrites = "0"
------------------------------------------------------------
fdiskでパーティション作成
----------------------------------------------------------
vmxファイルに以下を追加
disk.locking = "false"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0"
diskLib.dataCacheMinReadAheadSize = "0"
diskLib.dataCachePageSize = "4096"
diskLib.maxUnsyncedWrites = "0"
★oracleasmではない時のディスクグループの作り方
root で fdisk
# vi /etc/udev/rules.d/99-oracle.rules
KERNEL=="sd[b-z]1",ACTION=="add|change",OWNER="grid",GROUP="asmadmin",MODE="0660"
KERNEL=="sd[b-z]1",ACTION=="add|change",OWNER="grid",GROUP="asmadmin",MODE="0660"
# udevadm control --reload-rules
# start_udev
# start_udev
※CentOS では start_udevの代わりに以下を利用する。場合によっては reboot要(2行目で不要なはず)。
# udevadm control --reload-rules
# udevadm trigger
# udevadm control --reload-rules
# udevadm trigger
# ls -lrt /dev/sd*
brw-rw----. 1 grid asmadmin 8, 145 Jun 2 21:06 sdj1
brw-rw----. 1 grid asmadmin 8, 161 Jun 2 21:09 sdk1
brw-rw----. 1 grid asmadmin 8, 177 Jun 2 21:09 sdl1
brw-rw----. 1 grid asmadmin 8, 193 Jun 2 21:09 sdm1
brw-rw----. 1 grid asmadmin 8, 145 Jun 2 21:06 sdj1
brw-rw----. 1 grid asmadmin 8, 161 Jun 2 21:09 sdk1
brw-rw----. 1 grid asmadmin 8, 177 Jun 2 21:09 sdl1
brw-rw----. 1 grid asmadmin 8, 193 Jun 2 21:09 sdm1
上記の後に、create diskgroup <DG名> external redundancy disk '<device>' で上記デバイス名を指定する。
■tmpfsの追加
/etc/fstabに以下を追加
tmpfs /dev/shm tmpfs rw,exec,size=2500M 0 0
tmpfs /dev/shm tmpfs rw,exec,size=2500M 0 0
■rpm
# rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm ksh-20120801-22.el7_1.2.x86_64.rpm libaio-devel-0.3.109-13.el7.x86_64.rpm
# rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm ksh-20120801-22.el7_1.2.x86_64.rpm libaio-devel-0.3.109-13.el7.x86_64.rpm
■ホスト名:/etc/hostname(CentOS7)、/etc/sysconfig/network(CentOS6)、/etc/hosts の編集
ここでクローンを作ると楽。その場合は、以降、以下を実施する。
◎RAC側ホスト名:/etc/hostname(CentOS7)、/etc/sysconfig/network(CentOS6)、/etc/hosts の編集
◎IPアドレス変更:/etc/sysconfig/network-script/ifcfg-eno*を変更
■cvuqdisk-1.0.9-1.rpm のインストール (GridInfrastructureのメディアより)
■SSH (grid、root)
インストーラの「設定」or「setup」のボタンで設定可能。
ダメな場合は以下手順(全サーバで実施要):
client% ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/foo/.ssh/id_dsa): <- Enter
Created directory '/home/foo/.ssh'.
Enter passphrase (empty for no passphrase): <- Enter(パスフレーズなし)
Enter same passphrase again: <- Enter(パスフレーズなし)
Generating public/private dsa key pair.
Enter file in which to save the key (/home/foo/.ssh/id_dsa): <- Enter
Created directory '/home/foo/.ssh'.
Enter passphrase (empty for no passphrase): <- Enter(パスフレーズなし)
Enter same passphrase again: <- Enter(パスフレーズなし)
■エラー
▼PRVG-11850: ファイアウォールの解除が必要。
▼PRVF-5507: ntpdが存在する。
CentOS 6
service ntpd stop
service ntpd stop
/etc/ntp.conf、/var/run/ntpd.pid の削除
設定ファイル、pidファイルも削除
▼PRVG-1360
該当サービスを停止する。
▼PRCF-2020
?
?
▼失敗したASMをもう一度利用するには。
dd if=/dev/zero of=<ASMのデバイス名> bs=8192 count=20
▼CRS-0184
インターコネクト通信ができない?
ノード全部再起動
ノード全部再起動
▼Cluster Ready Service が起動しないとき
init.crsdは起動時に死ぬと、PIDファイルが残り、再起動時に失敗する。
起動オプション:
start 起動するフラグを立てる(/etc/init.d/init.crsから)
stop 停止する(/etc/init.d/init.crsから)
run 起動する(/etc/inittab から)
startを実施すると、再起動フラグが立つ。
start 起動するフラグを立てる(/etc/init.d/init.crsから)
stop 停止する(/etc/init.d/init.crsから)
run 起動する(/etc/inittab から)
startを実施すると、再起動フラグが立つ。
・起動済みのinit.crsdをKILL
・PIDファイルを消す。
・PIDファイルを消す。
/etc/init.d/init.crsd stop
/etc/init.d/init.crsd start
/etc/init.d/init.crsd start
▼INS-41210: 使用可能なネットワークのサブネットの1つが、Automatic Storage Management(ASM)用とマークされています。
原因: このストレージ・オプションは、ASMで使用するようにネットワーク・サブネットを構成する必要がありません。
原因: このストレージ・オプションは、ASMで使用するようにネットワーク・サブネットを構成する必要がありません。
インストーラのNetwork I/Fの選択肢で、ASMを選択から外す。
▼ASMで自動マウントさせるには
ASMインスタンスで、ASM_DISKGROUPSパラメータに追記する。
▼PRVE-0044
[root@primary01 ~]# vi /etc/security/limits.conf
...
oracle soft memlock unlimited
oracle hard memlock unlimited
...
oracle soft memlock unlimited
oracle hard memlock unlimited
■状態確認
crs_stat -t
crsctl status resource
crs_stat -t
crsctl status resource
srvctl status asm
srvctl status listener
srvctl status database -d <db>
srvctl status mgmtdb
srvctl status diskgroup -diskgroup <dg名>
srvctl status listener
srvctl status database -d <db>
srvctl status mgmtdb
srvctl status diskgroup -diskgroup <dg名>
# OCR、vote diskがASM内にある場合に、ASMを停止するには、rootで以下を実行(クラスタの一部として停止)
# この場合に、srvctl stop asmで止めようとするとORA-15027で停止できない(自分がつないでいるから
# 自分が停止できないという状態になる)。
crsctl stop crs
# この場合に、srvctl stop asmで止めようとするとORA-15027で停止できない(自分がつないでいるから
# 自分が停止できないという状態になる)。
crsctl stop crs
■patch
unzip
srvctl stop database -d <dbname>
opatch apply <patch#> # for DB
$ORACLE_HOME/crs/install/rootcrs.sh -prepatch
opatch apply <patch#> # for GI
$ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
$ORACLE_HOME/crs/install/rootcrs.sh -postpatch
srvctl start mgmtdb
srvctl start database -d <dbname>
unzip
srvctl stop database -d <dbname>
opatch apply <patch#> # for DB
$ORACLE_HOME/crs/install/rootcrs.sh -prepatch
opatch apply <patch#> # for GI
$ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
$ORACLE_HOME/crs/install/rootcrs.sh -postpatch
srvctl start mgmtdb
srvctl start database -d <dbname>
crsctl stop crs
crsctl start crs -wait
crsctl start crs -wait
ブログめも
■)とか:)とか ”)とか‘とか&#x0029 というようなUnicode表記で逃げる。バックスラッシュは、前に2つバックスラッシュ(計3つ)つけるとエスケープできる模様
|
Linuxめも
■まとめて処理するコマンド
pkill … 引数1にマッチしたプロセスIDにシグナルを送る。
pgrep … 引数1にマッチしたプロセスIDを列挙する。
pstree … プロセスの階層を表示する。
pkill … 引数1にマッチしたプロセスIDにシグナルを送る。
pgrep … 引数1にマッチしたプロセスIDを列挙する。
pstree … プロセスの階層を表示する。
■(Linux)コマンドを連結して行う「;」「&」「&&」「||」の違い
&&
前のコマンドがうまく終了した(終了ステータスが0)なら、次のコマンドを実行。そうでないなら次のコマンドは実行しない。
#configureが成功したらmake,makeが成功したらmake installする
configure && make && make install
&&
前のコマンドがうまく終了した(終了ステータスが0)なら、次のコマンドを実行。そうでないなら次のコマンドは実行しない。
#configureが成功したらmake,makeが成功したらmake installする
configure && make && make install
#/home/a.txtがあれば cp コマンドでコピーする
[ -f /home/a.txt ] && cp /home/a.txt /var/bkup
[ -f /home/a.txt ] && cp /home/a.txt /var/bkup
||
&&とは逆で前のコマンドに失敗(終了コード0以外)したら、次のコマンドを実行する。
#command1が失敗したら指定したディレクトリ削除
command1 || rm -rf /home/hoge/tmp
&&とは逆で前のコマンドに失敗(終了コード0以外)したら、次のコマンドを実行する。
#command1が失敗したら指定したディレクトリ削除
command1 || rm -rf /home/hoge/tmp
■{[0-9]*}
{} , {0} , {1000} , {12345678} など、{} 内に0個以上の数字がある文字列にマッチします。
{} , {0} , {1000} , {12345678} など、{} 内に0個以上の数字がある文字列にマッチします。
■パイプのステータスを取得する。$?は最後の結果のみ。
# true | true | false; echo ${PIPESTATUS[0]}
0
# true | true | false; echo ${PIPESTATUS[1]}
0
# true | true | false; echo ${PIPESTATUS[2]}
1
# true | true | false; echo ${PIPESTATUS[0]}
0
# true | true | false; echo ${PIPESTATUS[1]}
0
# true | true | false; echo ${PIPESTATUS[2]}
1
■一時ファイル不要のdiff(プロセス置換を使って一時ファイルを生成せずに置換)
diff <(sort file1.txt|uniq) <(sort file2.txt|uniq)
diff <(cat chklog.sh) <(cat chkres.sh )
diff <(sort file1.txt|uniq) <(sort file2.txt|uniq)
diff <(cat chklog.sh) <(cat chkres.sh )
■プロセス置換を使ってその後のログファイル出力を制御
以下のようにすると以降のコマンド実行結果は全てログにも出力される。
以下のようにすると以降のコマンド実行結果は全てログにも出力される。
■バイナリデータをsarで読む
sar -f /var/log/sa/saDD
※/var/log/sa/sarDD はテキスト
sar -f /var/log/sa/saDD
※/var/log/sa/sarDD はテキスト
■sarでバイナリデータ出力後、テキスト書式に変換する方法
sadf -T -- -A sar.out >sadf.txt
sadf -T -- -A sar.out >sadf.txt
■viで大文字⇒小文字変換
s/[A-Z]/?L&/g
s/[A-Z]/?L&/g
■tail -f をパイプでgrepするとバッファに保存されてしまう場合、リアルタイムに出す方法。
tail -f /home/hoge/hoge.log | grep --line-buffered "error"
tail -f /home/hoge/hoge.log | grep --line-buffered "error"
■tail -fでリアルタイムに文字コード変換
$ tail -f logfile | while read LINE ; do echo $LINE | iconv -f SJIS -t UTF-8 ; done
もしくは
$ tail -f logfile | nkf -u -S -w
$ tail -f logfile | while read LINE ; do echo $LINE | iconv -f SJIS -t UTF-8 ; done
もしくは
$ tail -f logfile | nkf -u -S -w
■空の配列を生成する
array=()
--
declare -a array=() # 宣言
declare -a array=("a" "b" "c") # 初期化
--
some_func() {
array=()
--
declare -a array=() # 宣言
declare -a array=("a" "b" "c") # 初期化
--
some_func() {
local local_array=() # 関数内スコープは local で定義
}
■ 配列のデータを追加
# 先頭に追加
array=(3 "${array[@]}") # array は (3 “a” "b" "c")
# 末尾に追加
array=("${array[@]}" 4) # array は (3 "a" "b" "c" 4)
array+=( 5 ) # array は (3 "a" "b" "c" 4 5)
# 先頭に追加
array=(3 "${array[@]}") # array は (3 “a” "b" "c")
# 末尾に追加
array=("${array[@]}" 4) # array は (3 "a" "b" "c" 4)
array+=( 5 ) # array は (3 "a" "b" "c" 4 5)
■ 配列からデータを取り出す
# データの先頭要素を取り出す(破壊的操作)
array=("${array[@]:1}") # array は ("a" "b" "c" 4 5)
# データの末尾要素取り出す(破壊的操作)
declare -i num=${#array[@]}-1
array=("${array[@]:0:$num}") # array は ("a" "b" "c" 4)
# 一行
array=(${array[@]:0:*5}) # array は ("a" "b" "c")
# データの先頭要素を取り出す(破壊的操作)
array=("${array[@]:1}") # array は ("a" "b" "c" 4 5)
# データの末尾要素取り出す(破壊的操作)
declare -i num=${#array[@]}-1
array=("${array[@]:0:$num}") # array は ("a" "b" "c" 4)
# 一行
array=(${array[@]:0:*5}) # array は ("a" "b" "c")
■ 配列のデータを参照する
i=0
for e in ${array[@]}; do
array[0] = a
array[1] = b
array[2] = c
i=0
for e in ${array[@]}; do
echo "array[$i] = ${e}"
let i++
donelet i++
array[0] = a
array[1] = b
array[2] = c
■ 配列のデータから任意の要素を削除する
# ある要素を削除
unset array[1]
echo "${array[@]}" # arrayは("a" "c")
echo ${array[0]} # array[0]は"a"
echo ${array[1]} # array[1]は ""(空)
echo ${array[2]} # array[2]は"c"
配列の中身のデータは削除できても、配列自体は削除できない。
添字を詰めたければ、
array=("${array[@]}"
代入しなおす必要があり。
# ある要素を削除
unset array[1]
echo "${array[@]}" # arrayは("a" "c")
echo ${array[0]} # array[0]は"a"
echo ${array[1]} # array[1]は ""(空)
echo ${array[2]} # array[2]は"c"
配列の中身のデータは削除できても、配列自体は削除できない。
添字を詰めたければ、
array=("${array[@]}"
代入しなおす必要があり。
■ 配列にデータを格納する
array[0]=1
array[1]="a"
array[0]=1
array[1]="a"
■hangup時に強制的にkdumpを行って再起動する操作:(マジックキー)
Alt+SysRq+c (カーネルをクラッシュさせる)->crash時にkdumpが吐ける設定になっていないと意味がない。
Alt+SysRq+b 即座に再起動
Alt+SysRq+c (カーネルをクラッシュさせる)->crash時にkdumpが吐ける設定になっていないと意味がない。
Alt+SysRq+b 即座に再起動
■チルダ問題メモ
FULL WIDTH TILDE(15711646)とWAVE DASH(14909596)を切りかえて再調査。WAVE_DASHは済み。
FULL WIDTH TILDE(15711646)とWAVE DASH(14909596)を切りかえて再調査。WAVE_DASHは済み。
■LinuxでNTPサーバと時刻同期していることを確認する方法。
ntpq -p
ntpq -p
remote refid st t when poll reach delay offset jitter
==============================================================================*10.XX.X.XXX LOCAL(0) 6 u 423 1024 377 1.758 -0.275 1.677
LOCAL(0) LOCAL(0) 5 l 64 64 377 0.000 0.000 0.008
remoteのところにつき記号の意味は以下の通り。*:現在参照同期中のサーバ
+:現在クロック誤り検査に合格したサーバ
#:現在参照同期中ですが,距離が遠いサーバ
:現在参照していないサーバ (空白)
x:現在クロック誤り検査で不合格になったサーバ
.:現在参照リストからはずされたサーバ
■NTPサーバと時刻合わせ
ntpdate NTPサーバ
ntpdate NTPサーバ
■CentOSでhwclockをlocaltimeに設定する方法(9時間差を補正する)
# timedatectl
# hwclock -s --localtime
# timedatectl set-local-rtc true
# timedatectl
# hwclock -s --localtime
# timedatectl set-local-rtc true
systemd-timedated が /etc/adjtime を読み込む。このファイルの3行目がhwclockがUTCかLOCALかを設定。systemd-timedatedがそれを見て、システムクロックをUTCかLOCALに設定する。timedatectl set-local-rtc true で当該ファイル3行目をLOCALに変更する。3行目がないとUTCと判断してにする模様。
1行目は、時刻の補正やズレ、2行目は、時刻合わせ後の経過秒数。0は未実施か、異常であった場合の模様。
1行目は、時刻の補正やズレ、2行目は、時刻合わせ後の経過秒数。0は未実施か、異常であった場合の模様。
・/etc/adjtime
# more /etc/adjtime
0.0 0 0.0
0
LOCAL
# more /etc/adjtime
0.0 0 0.0
0
LOCAL
■CentOS7で rootユーザのパスワードリセット
wheelグループのユーザでログインし以下を実行する。
$ sudo passwd root
wheelグループのユーザでログインし以下を実行する。
$ sudo passwd root
※GRUB 2ではシングルユーザモード(エマージェンシーモードも同様)でもrootユーザのパスワードが要求される模様。
■Linuxにおける変数の設定
/etc/environment ファイル
ログイン時にOSが最初に使用するファイル。すべてのプロセス用の基本的な変数を記述。
シェルセッションを起動するさまざまなプログラムが /etc/environment ファイルを使う。組織で利用するHTTP_PROXY環境変数等に使う。
ログイン時にOSが最初に使用するファイル。すべてのプロセス用の基本的な変数を記述。
シェルセッションを起動するさまざまなプログラムが /etc/environment ファイルを使う。組織で利用するHTTP_PROXY環境変数等に使う。
/etc/profile ファイル
ログイン時にOSが使用する 2 番目のファイル。
bash は対話型シェル用に /etc/bash.bashrc を使い、ログインシェル用に /etc/profile を使う。
ログイン時にOSが使用する 2 番目のファイル。
bash は対話型シェル用に /etc/bash.bashrc を使い、ログインシェル用に /etc/profile を使う。
.env ファイル
OSがログイン時に使用する 4 番目のファイル。.profile に export ENV=$HOME/.env の行が含まれている場合は、.env ファイル。
OSがログイン時に使用する 4 番目のファイル。.profile に export ENV=$HOME/.env の行が含まれている場合は、.env ファイル。
全シェルを同様設定にする
~/.bash_profile ファイルの中で ~/.bashrc の内容を実行 またはsourceする。
全ユーザで行う場合は、/etc/profile の中で /etc/bash.bashrc を呼ぶ。
~/.bash_profile ファイルの中で ~/.bashrc の内容を実行 またはsourceする。
全ユーザで行う場合は、/etc/profile の中で /etc/bash.bashrc を呼ぶ。
■ログイン画面で、SHIFT+CTRL+F1をおすと、CUIの画面が開くことができる。
■/etc/environment例
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
LANGUAGE=ja_JP:en
LANG=ja_JP.UTF-8
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
LANGUAGE=ja_JP:en
LANG=ja_JP.UTF-8
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8
■/etc/default/locale例
LANG=ja_JP.UTF-8
LANGUAGE=ja_JP:en
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8
LANG=ja_JP.UTF-8
LANGUAGE=ja_JP:en
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8
■/etc/pam.d/gdm例
auth required pam_env.so readenv=1 envfile=/etc/default/locale
auth required pam_env.so readenv=1 envfile=/etc/default/locale
永続化したい場合、/etc/fstabを以下に変更
STATSPACKめも
●スケジュール確認
select * from dba_jobs;
select * from dba_jobs;
●取得状況 PERFSTATユーザ
alter session set nls_date_format='yyyy/mm/dd HH24:MI:SS';
select snap_id,snap_time from stats$snapshot order by snap_id;
alter session set nls_date_format='yyyy/mm/dd HH24:MI:SS';
select snap_id,snap_time from stats$snapshot order by snap_id;
●STATSPACKの構築(sysで実施)
@?/rdbms/admin/spcreate
@?/rdbms/admin/spcreate
●STATSPACKの定義削除(sysで実施)
@?/rdbms/admin/spdrop
@?/rdbms/admin/spdrop
●STATSPACK自動スケジューリング
@?/rdbms/admin/spauto
@?/rdbms/admin/spauto
●STATSPACK JOB間隔の変更
execute dbms_job.interval(1,'SYSDATE+(1/48)');
execute dbms_job.interval(1,'SYSDATE+(1/48)');
●過去のsnapshotを削除
-- スナップショットIDの範囲を指定して削除
execute statspack.purge(i_begin_snap=>91, i_end_snap=>115, i_extended_purge=>TRUE)
--特定日時以前に取得したスナップショットを削除
execute statspack.purge(i_purge_before_date=>to_date('20001231','YYYYMMDD'), i_extended_purge=>TRUE)
--指定日数分のスナップショットを残して削除
execute statspack.purge(i_num_days=>30, i_extended_purge=>TRUE)
-- スナップショットIDの範囲を指定して削除
execute statspack.purge(i_begin_snap=>91, i_end_snap=>115, i_extended_purge=>TRUE)
--特定日時以前に取得したスナップショットを削除
execute statspack.purge(i_purge_before_date=>to_date('20001231','YYYYMMDD'), i_extended_purge=>TRUE)
--指定日数分のスナップショットを残して削除
execute statspack.purge(i_num_days=>30, i_extended_purge=>TRUE)
●スナップショットレベルの確認
select snap_level from stats$statspack_parameter;
select snap_level from stats$statspack_parameter;
●スナップショットレベルの変更(スナップショットレベルを7に変更する場合)
exec statspack.modify_statspack_parameter (i_snap_level=>7);
exec statspack.modify_statspack_parameter (i_snap_level=>7);
●STATSPACKをimportする PERFSTATユーザ
spool dis.sql
select 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';' from user_constraints;
spool off
@dis
imp PERFSTAT/<pass> file=<expしたdmp> log=spuimp.log ignore=y
spool dis.sql
select 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';' from user_constraints;
spool off
@dis
imp PERFSTAT/<pass> file=<expしたdmp> log=spuimp.log ignore=y
●初期化パラメータの確認
select * from stats$parameter;
select * from stats$parameter;
●SQLテキスト取得
select + from stats$sqltext;
select + from stats$sqltext;
SQL性能改善めも
●オプティマイザのパラメータ確認
select * from V$SYS_OPTIMIZER_ENV;
select * from V$SES_OPTIMIZER_ENV:
select * from V$SES_OPTIMIZER_ENV:
●オプティマイザヒント確認
select * from v$sql_hint;
●各オブジェクトのSELECTIVITY確認
セレクティビティ(選択率) = 条件を適用した結果の行数 / 全体の行数 カーディナリティ = 表の行数 × セレクティビティ
select count(distinct 列名1), count(distinct 列名2), … count(distinct 列名N)
from 対象表名
from 対象表名
●plan_tableの作成
●実行計画の生成
explain plan for SQL文
●実行計画確認
●PLAN_TABLE別名
CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$
●SQL文確認
select * from v$sqltext;
●SQLによる確認
select * from v$sql;
select * from v$sqlarea;
select * from v$sqlarea;
●SQL実測確認
set pages 50000
set lines 30000
set trimspool on
set timing on
set time on
set autot on
spool <スプールファイル名>
<対象SQL実行>
spool off
set lines 30000
set trimspool on
set timing on
set time on
set autot on
spool <スプールファイル名>
<対象SQL実行>
spool off
●実行中のSQL確認