SQL Server Reorganize All Indexes in Database

declare @tableName nvarchar(500)
declare @indexName nvarchar(500)
declare @indexType nvarchar(55)
declare @percentFragment decimal(11,2)

declare FragmentedTableList cursor for
 SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
   ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
   indexstats.avg_fragmentation_in_percent
 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
   INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
        AND ind.index_id = indexstats.index_id
  WHERE
-- indexstats.avg_fragmentation_in_percent , e.g. >30, you can specify any number in percent
   indexstats.avg_fragmentation_in_percent > 5
  AND ind.Name is not null
  ORDER BY indexstats.avg_fragmentation_in_percent DESC

    OPEN FragmentedTableList
    FETCH NEXT FROM FragmentedTableList 
    INTO @tableName, @indexName, @indexType, @percentFragment

    WHILE @@FETCH_STATUS = 0
    BEGIN
      print 'Processing ' + @indexName + ' on table ' + @tableName + ' which is ' + cast(@percentFragment as nvarchar(50)) + ' fragmented'
     
      if(@percentFragment<= 30)
      BEGIN
            EXEC( 'ALTER INDEX [' +  @indexName + '] ON ' + @tableName + ' REBUILD; ')
       print 'Finished reorganizing ' + @indexName + ' on table ' + @tableName
      END
      ELSE
      BEGIN
         EXEC( 'ALTER INDEX [' +  @indexName + '] ON ' + @tableName + ' REORGANIZE;')
        print 'Finished rebuilding ' + @indexName + 'on table ' + @tableName
      END 
      FETCH NEXT FROM FragmentedTableList 
        INTO @tableName, @indexName, @indexType, @percentFragment
    END
    CLOSE FragmentedTableList
    DEALLOCATE FragmentedTableList

SSH Login Without Password Using ssh-keygen & ssh-copy-id

1. create public key
[red.red-pc] ➤ ssh-keygen

WARNING:

You should not store anything in MobaXterm HOME directoy (/home/mobaxterm): with your current settings, this folder is not "persistent", so it will be cleared at each MobaXterm restart.

If you want to set a "persistent" HOME directory which will not be cleared at each MobaXterm startup, go to MobaXterm settings window and choose a folder in which to store MobaXterm home files.

Generating public/private rsa key pair.
Enter file in which to save the key (/home/mobaxterm/.ssh/id_rsa):
/home/mobaxterm/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/mobaxterm/.ssh/id_rsa.
Your public key has been saved in /home/mobaxterm/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:InGPnZ7IvfhE/ejPBFOOsdoWGGVitbmKm7T/4M2RsD8 red@red-pc

2. copy the public key to remote host

[red.red-pc] ➤ ssh-copy-id -i id_rsa.pub 10.123.105.119

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

red@10.123.105.119's password:

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '10.123.105.119'"

and check to make sure that only the key(s) you wanted were added.

3. login to remote host whitout password

[red.red-pc] ➤ ssh root@10.123.105.119

Last login: Thu Jul 12 16:26:06 2018 from 10.123.105.101

Python Get Script Path

print(os.path.dirname(os.path.realpath(__file__)))
or
print(os.path.dirname(os.path.realpath(sys.argv[0])))

SQL Server Refresh All View

SET NOCOUNT ON
DECLARE @SQL varchar(max) = ''
SELECT @SQL = @SQL + 'print ''Refreshing --> ' + name + '''
EXEC sp_refreshview ' + name + ';
'
  FROM sysobjects
  WHERE type = 'V' --< condition to select all views, may vary by your standards
--SELECT @SQL
EXEC(@SQL)

SQL server long running queries query stats

SELECT TOP 100
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.execution_count,
    SUBSTRING (qt.text,qs.statement_start_offset/2,
         (CASE WHEN qs.statement_end_offset = -1
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
    o.name AS object_name,
    DB_NAME(qt.dbid) AS database_name
  FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID()
  ORDER BY average_seconds DESC;

SQL Server unused indexes

select object_name(i.object_id) as ObjectName, i.name as [Unused Index],MAX(p.rows) Rows
,8 * SUM(a.used_pages) AS 'Indexsize(KB)',
case
when i.type = 0 then 'Heap'
when i.type= 1 then 'clustered'
when i.type=2 then 'Non-clustered' 
when i.type=3 then 'XML' 
when i.type=4 then 'Spatial'
when i.type=5 then 'Clustered xVelocity memory optimized columnstore index' 
when i.type=6 then 'Nonclustered columnstore index'
end index_type,
'DROP INDEX ' + i.name + ' ON ' + object_name(i.object_id) 'Drop Statement'
from sys.indexes i
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id
     and i.index_id = s.index_id
     and s.database_id = db_id()
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
where objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
and s.index_id is null -- and dm_db_index_usage_stats has no reference to this index
or (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)-- index is being updated, but not used by seeks/scans/lookups
GROUP BY object_name(i.object_id) ,i.name,i.type
order by object_name(i.object_id) asc

WCF Service IParameterInspector kullanımı

public class ParameterValidator : IParameterInspector
{
public void AfterCall(string operationName, object[] outputs, object returnValue, object correlationState)
{
           
}

public object BeforeCall(string operationName, object[] inputs)
{
            return null;
}
}

public class CustomDataOperationBehavior : Attribute, IOperationBehavior
{
public void AddBindingParameters(OperationDescription operationDescription, BindingParameterCollection bindingParameters)
{
           
}

public void ApplyClientBehavior(OperationDescription operationDescription, ClientOperation clientOperation)
{
           
}

public void ApplyDispatchBehavior(OperationDescription operationDescription, DispatchOperation dispatchOperation)
{
            dispatchOperation.ParameterInspectors.Add(new ParameterValidator());
}

public void Validate(OperationDescription operationDescription)
{
           
}

[CustomDataOperationBehavior]
public BaseDataTable_Response GetDataTable(int rowCount)

Google Maps Api için şehir, bölge, ülke sınırları

1. http://nominatim.openstreetmap.org/ adresinden istenilen şehir arandıktan sonra listeden uygun sonuç seçilip "Details" tıklanır

2. Açılan sayfada listeden OSM Id kopyalanır. Ankara için örnek 223422

3. http://polygons.openstreetmap.fr/index.py adresindeki "Id of relation" a Id (223422) yapıştırılıp "Gönder" düğmesine tıklanır.

Açılan sayfada WKT, GeoJSON, poly yada image formatında ilgili şehir için poligon bilgileri alınabilir.

İlçe, İl, Bölge, Ülke ile ilgili sınır bilgileri bu şekilde elde edilebilir.

Daha sonra bu bilgilerle google maps api üzerinde çizim yaptırılabilir.

Weblogic SSL Konfigürasyonu

İşlemlerde örnek olarak /app/ssl dizini kullanılmıştır

keytool -genkey -keyalg RSA -alias medrec -keystore identity.jks -validity 360 -keysize 2048

keytool -export -alias medrec -file medrec.crt -keystore identity.jks

keytool -import -trustcacerts -alias medrec -file medrec.crt -keystore trust.jks

Weblogic console üzerinden ilgili server için SSL portu açılır

İlgili serverda Keystore sekmesinde aşağıdaki ayarlar yapılır

Keystores : Custom Identity and Custom Trust
Custom Identity Keystore : /app/sslidentity.jks
Custom Identity Keystore Type : JKS
Custom Identity Keystore Passphrase : changeit (identity.jks için hangi şifre belirlenmişse)
Confirm Custom Identity Keystore Passphrase : changeit

Custom Trust Keystore : /app/ssl/trust.jks
Custom Trust Keystore Type : JKS
Custom Trust Keystore Passphrase : changeit (trust.jks için hangi şifre belirlenmişse)
Confirm Custom Trust Keystore Passphrase : changeit

SSL tanımı yapılır. (SSL sekmesi)
Confirm Custom Trust Keystore Passphrase : medrec
Private Key Passphrase : changeit
Confirm Private Key Passphrase : changeit
Hostname Verification : none seçilir

Linux terminalde username@hostname:working directory görüntülenmesi

Login olunan sunucuda user@hostname:working_directory şeklinde komut satırını görmek için aşağıdaki tanımın .bashrc yada .bash_profile dosyasına eklenmesi yeterli olacaktır

export PS1='\u@\h:\w\$ '

appadmin@alpullu:/$