Sunday, November 13, 2011

Working with SQL Server fragmentation

use AdventureWorks2008R2;
go
select * from
(select * from sys.dm_db_index_physical_stats(db_id('AdventureWorks2008R2'),null,null,null,null)) t
where t.avg_fragmentation_in_percent> 30
GO

use AdventureWorks2008R2;
go
select * from sys.dm_db_index_physical_stats(db_id(),414624520,null,null,null)
GO


select * from sys.indexes where object_id=414624520 and index_id=1;
select * from sys.tables where object_id=414624520;
select * from sys.schemas where schema_id=9;

alter index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct rebuild with (ONLINE=on);
GO

Tuesday, November 8, 2011

T-SQL code to enable SQL Logins to create agent job

USE [msdb]
GO
CREATE USER [agentoper] FOR LOGIN [agentoper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [agentoper]
GO

Saturday, November 5, 2011

Get help to use DBCC in SQL server

dbcc help ('?');
go

================================================================================================
checkalloc

checkcatalog

checkconstraints

checkdb

checkfilegroup

checkident

checktable

cleantable

dbreindex

dropcleanbuffers

free

freeproccache

freesessioncache

freesystemcache

help

indexdefrag

inputbuffer

opentran

outputbuffer

pintable

proccache

show_statistics

showcontig

shrinkdatabase

shrinkfile

sqlperf

traceoff

traceon

tracestatus

unpintable

updateusage

useroptions

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================

dbcc help ('checkdb');
go

================================================================================================
dbcc checkdb
(
{ 'database_name' | database_id | 0 }
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)
[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ DATA_PURITY ] ]
[ , [ EXTENDED_LOGICAL_CHECKS ] ]
}
]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help (sqlperf);
go

================================================================================================
dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc sqlperf ( LOGSPACE )
================================================================================================
Database Name Principal Filegroup Name Log Size (MB) Log Space Used (%) Status
-------------------------- -------------------------- ------------- ------------------ -----------
master PRIMARY 0.7421875 61.57895 0
tempdb PRIMARY 0.9921875 51.08268 0
model PRIMARY 0.7421875 46.05263 0
msdb PRIMARY 0.7421875 58.28947 0
ReportServer$PROD PRIMARY 6.867188 19.17662 0
ReportServer$PRODTempDB PRIMARY 1.054688 37.03704 0
AdventureWorks2008R2 PRIMARY 1.492188 39.39791 0
AdventureWorksDW2008R2 PRIMARY 0.484375 80.04032 0
TESTDB PRIMARY 0.9921875 37.45079 0

(9 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.


http://msdn.microsoft.com/en-us/library/ms176040.aspx

Friday, November 4, 2011

Different termination options to set sql server in single_user/offline mode

ALTER DATABASE [TestDB] SET
SINGLE_USER
-- RESTRICTED_USER
-- OFFLINE
WITH
-- ROLLBACK AFTER 5 SECONDS
ROLLBACK IMMEDIATE
-- NO_WAIT;

Saturday, October 22, 2011

Comparision between shrink space, shrink space compact and shrink space cascade

drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space compact;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space compact; -- same as 'alter index big_table_pk coalesce'
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 coalesce;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild online;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';

/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space compact
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 coalesce;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 1376256
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild online;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space cascade;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 65536
BIG_TABLE_N1 1376256
BIG_TABLE 1310720
****************************************************************************************************/

Segment Advisor related SQL



/*
create table big_table (id number, name char(200));
insert into big_table select rownum,'a' from dual connect by rownum<100000;
delete from big_table where mod(id,10)<>0;
commit;
*/

SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'FALSE', SHOW_MANUAL=>'FALSE',SHOW_FINDINGS=>'FALSE'));



SELECT 'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f ,
dba_advisor_objects o ,
dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;




DECLARE
my_task_id NUMBER;
obj_id NUMBER;
my_task_name VARCHAR2(100);
my_task_desc VARCHAR2(500);
BEGIN
my_task_name := 'Adhoc Segment Advice for table Donghua.BIG_TABLE';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1
---------
dbms_advisor.create_task ( advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object ( task_name => my_task_name,
object_type => 'TABLE',
attr1 => 'DONGHUA',
attr2 => 'BIG_TABLE',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter( task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/


SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));





-- 10gR2 view
-- DBA_AUTO_SEGADV_SUMMARY

select * from dictionary where table_name like '%AUTO%';

select job_start_time,job_status,job_duration,job_error
from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
order by job_start_time;

select * from dba_autotask_client;



select * from DBA_HIST_SEG_STAT;

Using SSH tunneling to access database through ssh

rh6 is the client, vmxdb04b is the database server,

firewall port open is incomming port 22 for vmxdb04b


donghua@rh6:~$ ssh -N -p 22 oracle@vmxdb04b -L 8888/vmxdb04b/1521

oracle@vmxdb04b's password:


-p 22 oracle@vmxdb04b: build ssh connection to vmxdb04b


-L 8888/vmxdb04b/1521 : Start a local listening port 8888 on rh6, and forward all the packet data to vmxdb04b:1521. Here the vmxdb04b can change to any host for forwarding purpose.

-N instructs OpenSSH to not execute a command on the remote system.


donghua@rh6:~$ netstat -na|more

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 127.0.0.1:8307 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:8888 0.0.0.0:* LISTEN



donghua@rh6:~$ sqlplus donghua/donghua@localhost:8888/orcl

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 09:40:32 2011

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username,program,machine,paddr from v$session where sid=userenv('SID');

USERNAME PROGRAM

------------------------------ ------------------------------------------------

MACHINE

----------------------------------------------------------------

PADDR

----------------

DONGHUA sqlplus@rh6 (TNS V1-V3)

rh6

00000000914A0B88