DatabaseNotes

=PgSQL Spesifik=

Tablo Kopyala ve Geri yükle
Komut satırı açıldıktan sonra,

Backup için:

"C:\Program Files\PostgreSQL\11\bin\psql.exe" -U postgres -d scadatest1 -c "\copy (SELECT * FROM channels ORDER BY kanal_id) TO STDOUT" > "D:\ch.tsv"

Restore için:

"C:\Program Files\PostgreSQL\11\bin\psql.exe" -U postgres -d postgres -c "\copy channels FROM 'D:\ch.tsv"'"

Restore ederken Unique Id Constraint lerden dolayı çalışma olabilir. Constrainti kaldırmak gerekebilir

Tablo Boyutlarını Görüntüleme
SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Aralıktan Rasgele veri çekme
Faster: SELECT * FROM (SELECT tag_id,data_value,logtime,logdate FROM logs.tag_log WHERE tag_id=9463 AND logtime > 1484749123220 AND logtime < 1489449123220 and random < 0.1 ) X  ORDER BY logtime

Slower SELECT * FROM (SELECT tag_id,data_value,logtime,logdate FROM logs.tag_log WHERE tag_id=9463 AND logtime > 1484749123220 AND logtime < 1489449123220 ORDER BY random limit 1000) X  ORDER BY logtime

Distribute data on multiple disks
Create four tablespaces each one on a different disk (see: http://www.postgresql.org/docs/current/static/sql-createtablespace.html)

Move the content of the tables to the tablespaces so that the data is distributed as you intend it to be using ALTER TABLE foobar SET tablespace foospace, see here: http://www.postgresql.org/docs/current/static/sql-altertable.html

Move the content of the tables' indexes to the tablespaces using alter index idx_foo set tablespace spacefoo, see here: http://www.postgresql.org/docs/current/static/sql-alterindex.html

=Oracle Spesifik=

Expire olan parola
https://stackoverflow.com/questions/1095871/how-do-i-turn-off-oracle-password-expiration

Giriş yapabilmek için:

cmd

c:\sqlplus

Enter user-name: system Enter password: ****

Burda şifre guncelleme soracak

Ya da:

select profile from DBA_USERS where username = 'SYSTEM'; alter profile DEFAULT limit password_life_time UNLIMITED;

TableSpace size yetersiz
Mevcut tablespaceleri gormek için:

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name", Substr(df.file_name,1,40) "File Name", Round(df.bytes/1024/1024,2) "Size (M)", Round(e.used_bytes/1024/1024,2) "Used (M)", Round(f.free_bytes/1024/1024,2) "Free (M)", Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used" FROM DBA_DATA_FILES DF, (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) E, (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name;

Table space i arttırmak için:

ALTER TABLESPACE "USERS" ADD DATAFILE 'F:\ORADATA\ORCL\DATAFILE\O1_MF_USERS_D99' SIZE 30000M

Tablo Boyutu Sorgulama
select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='TAG_LOG';

Uid yetersiz geldiğinde Sequence i düzeltmek
// Burda yazan muski.ISEQ$$_XXXX i dogru sequence no ile değiştirin

create or replace PROCEDURE muski.fix_sequence_tag IS l_MaxVal  pls_integer; l_Currval pls_integer default - 1; BEGIN select max(tag_id) into l_MaxVal from muski.tags; while l_Currval < l_Maxval loop select muski.ISEQ$$_92613.nextval into l_Currval from dual; end loop; END; CREATE OR REPLACE PROCEDURE muski.fix_sequence_ch IS  l_MaxVal  pls_integer; l_Currval pls_integer default - 1; BEGIN select max(kanal_id) into l_MaxVal from channels; while l_Currval < l_Maxval loop select muski.ISEQ$$_95731.nextval into l_Currval from dual; end loop; END;

begin muski.fix_sequence_tag;  end; begin muski.fix_sequence_ch;  end;

DB Maintanace
ALTER INDEX MUSKI.tag_log_pkey rebuild ALTER INDEX MUSKI.tag_log_agrhour_pkey rebuild

Oracle Archive DB BAT script
Save this as a bat file: set /p ProjectDbName= Enter Project Name: ECHO.%ProjectDbName% set /p ProjectDbPwd= Enter DB System Password : ECHO.%ProjectDbPwd% set /p ArchiveTime= Archive Data Before Time(in msecs from epoch): ECHO.%ArchiveTime% set /p StartArchive= Do you want to start archive process?(YES / NO(DEFAULT)) : ECHO.%StartArchive% IF NOT "%StartArchive%" == "YES" (exit) @echo CREATE OR REPLACE DIRECTORY orcl_full as 'E:\ORACLE_DUMP\'; | sqlplus / as sysdba @echo GRANT read,write ON DIRECTORY orcl_full to DB_MANAGER; | sqlplus / as sysdba @echo GRANT datapump_exp_full_database to DB_MANAGER; | sqlplus / as sysdba del /q E:\ORACLE_DUMP\* ECHO Exporting archive from MAIN DB expdp DB_MANAGER/%ProjectDbPwd% DIRECTORY=orcl_full DUMPFILE=archive-metadata.dat LOGFILE=full_exp.log TABLES=%ProjectDbName%.TAG_LOG,%ProjectDbName%.TAG_LOG_AGRHOUR,%ProjectDbName%.TAG_LOG_AGRDAY,%ProjectDbName%.TAG_LOG_AGRWEEK,%ProjectDbName%.TAG_LOG_AGRMONTH,%ProjectDbName%.TAG_LOG_AGRYEAR   QUERY=%ProjectDbName%.TAG_LOG:\"where logtime < %ArchiveTime%\",%ProjectDbName%.TAG_LOG_AGRHOUR:\"where logtime < %ArchiveTime%\",%ProjectDbName%.TAG_LOG_AGRDAY:\"where logtime < %ArchiveTime%\",%ProjectDbName%.TAG_LOG_AGRWEEK:\"where logtime < %ArchiveTime%\",%ProjectDbName%.TAG_LOG_AGRMONTH:\"where logtime < %ArchiveTime%\",%ProjectDbName%.TAG_LOG_AGRYEAR:\"where logtime < %ArchiveTime%\" expdp DB_MANAGER/%ProjectDbPwd% DIRECTORY=orcl_full DUMPFILE=archive-metadata2.dat LOGFILE=full_exp2.log TABLES=%ProjectDbName%.TAGS,%ProjectDbName%.ALARMS,%ProjectDbName%.CHANNELS,%ProjectDbName%.USERS,%ProjectDbName%.USER_GROUPS,%ProjectDbName%.ACCESS_RIGHTS,%ProjectDbName%.REPORT_QUERIES set /p ContinueClean= Export Completed. Continue archive process?(NO / YES(DEFAULT)) : ECHO.%ContinueClean% IF "%ContinueClean%" == "NO" (exit) ECHO Create ARCHIVE DB @echo CREATE USER %ProjectDbName%_ARCHIVE IDENTIFIED BY "%ProjectDbPwd%"; | sqlplus / as sysdba @echo GRANT CREATE session TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo GRANT CREATE table TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo GRANT CREATE SEQUENCE TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo GRANT CREATE ANY PROCEDURE TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo GRANT EXECUTE ANY PROCEDURE TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo GRANT CREATE ANY TRIGER TO %ProjectDbName%_ARCHIVE; | sqlplus / as sysdba @echo ALTER USER %ProjectDbName%_ARCHIVE quota unlimited on USERS; | sqlplus / as sysdba ECHO Cleaning ARCHIVE DB @echo TRUNCATE %ProjectDbName%_ARCHIVE.TAGS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.ALARMS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.CHANNELS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.USERS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.USER_GROUPS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.ACCESS_RIGHTS CASCADE ; | sqlplus / as sysdba @echo TRUNCATE %ProjectDbName%_ARCHIVE.REPORT_QUERIES CASCADE ; | sqlplus / as sysdba impdp DB_MANAGER/%ProjectDbPwd% DIRECTORY=orcl_full DUMPFILE=archive-metadata.dat LOGFILE=full_exp3.log TABLE_EXISTS_ACTION=APPEND TABLES=%ProjectDbName%.TAG_LOG,%ProjectDbName%.TAG_LOG_AGRHOUR,%ProjectDbName%.TAG_LOG_AGRDAY,%ProjectDbName%.TAG_LOG_AGRWEEK,%ProjectDbName%.TAG_LOG_AGRMONTH,%ProjectDbName%.TAG_LOG_AGRYEAR remap_schema=%ProjectDbName%:%ProjectDbName%_ARCHIVE impdp DB_MANAGER/%ProjectDbPwd% DIRECTORY=orcl_full DUMPFILE=archive-metadata2.dat LOGFILE=full_exp4.log TABLE_EXISTS_ACTION=APPEND TABLES=%ProjectDbName%.TAGS,%ProjectDbName%.ALARMS,%ProjectDbName%.CHANNELS,%ProjectDbName%.USERS,%ProjectDbName%.USER_GROUPS,%ProjectDbName%.ACCESS_RIGHTS,%ProjectDbName%.REPORT_QUERIES remap_schema=%ProjectDbName%:%ProjectDbName%_ARCHIVE set /p ContinueClean= Import Completed. Continue Cleaning archived data from main DB?(NO / YES(DEFAULT)) : ECHO.%ContinueClean% IF "%ContinueClean%" == "NO" (exit) ECHO Cleaning MAIN DB @echo DELETE FROM %ProjectDbName%.TAG_LOG WHERE logtime^^^<%ArchiveTime%; | sqlplus / as sysdba @echo DELETE FROM %ProjectDbName%.TAG_LOG_AGRHOUR WHERE logtime^^^<%ArchiveTime%; | sqlplus / as sysdba ECHO Operation Completed...

=Bazı SQL Sorguları=

Bu sorguları Oracle da çalıştırırken 'public.' ve 'logs.' schema isimlerinin silinmesi gerekir

Kanal id si 489 ve  tipi standart  olan etiketlerin fonksiyon kodunu 3 yap
--UPDATE public.tags SET polfreq = 3 WHERE kanal_id=489 AND tagtype=1

5. kanaldaki tüm tagleri 3. kanala aktar
--UPDATE public.tags SET kanal_id = 3 WHERE kanal_id = 5

İsminde "_ABCD" geçen bütün etiketlerin deviceaddress ini 99 yap
--UPDATE public.tags SET deviceaddress = 99 WHERE tagname LIKE '%_ABCD%'

Bütün etiket isimlerinde "ABCD" gecenleri "EFGH" olarak değiştir
--UPDATE public.tags SET tagname = replace(tagname, 'ABCD', 'EFGH')

495 nolu kanal altındaki etiket isimlerinde "ABCD" gecenleri "EFGH" olarak değiştir
--UPDATE public.tags SET tagname = replace(tagname, 'ABCD', 'EFGH') WHERE kanal_id=495

Etiket adı "copy_FCU_Kat1_Linye5" ile başlayan tag leri 495 nolu kanala taşı
--UPDATE public.tags SET kanal_id = 495 WHERE tagname LIKE 'copy_FCU_Kat1_Linye5%'

Etiket adı "FCU_Kat1_Linye6_119_" ile başlayan tag leri listele
--select * from public.tags  WHERE tagname LIKE 'FCU_Kat1_Linye6_119_%'

489 ve 490 nolu kanalların altındaki etiketlerin adında geçen 'FCU_Zemin_Linye1_29_' leri 'FCU_Kat1_Linye3_183_' olarak değiştir
--UPDATE public.tags SET tagname = replace(tagname, 'FCU_Zemin_Linye1_29_', 'FCU_Kat1_Linye3_183_') WHERE kanal_id=489 OR kanal_id=490

130 nolu kanal altındaki etiket isimleri "FCU_Zemin_Linye3_1_" ile baslayan etiketleri sil
--DELETE FROM tags where kanal_id=130 AND tagname LIKE 'FCU_Zemin_Linye3_1_%'

60 ve 45 nolu kanal altındaki etiketlerin içerisinde "_Schedule" kelimesi geçen etiketleri Veritabanına Logla seçeneğini aktifleştir
--UPDATE public.tags SET islogging = 1 where kanal_id = 60 OR kanal_id = 45)AND tagname  LIKE '%_Schedule%'

60 ve 45 nolu kanal altındaki etiketlerin içerisinde "_Schedule" kelimesi geçen etiketleri Değişimde veritabınan logla
--UPDATE public.tags SET loggingfreq = 0 where (kanal_id = 60 OR kanal_id = 45)AND tagname  LIKE '%_Schedule%'

60 ve 45 nolu kanal altındaki etiketlerin içerisinde "_Schedule" kelimesi geçen etiketleri Periyodik olarak 300 saniyede veritabınına logla.
--UPDATE public.tags SET loggingfreq = 300 where (kanal_id = 60 OR kanal_id = 45)AND tagname  LIKE '%_Schedule%'

Bu kanalların(215 503 214 219 ...) altındaki adının içinde '_RoomOperating' etiketleri listele
--select * from public.tags where (kanal_id=215 OR kanal_id=503 OR kanal_id=214 OR kanal_id=219 OR kanal_id=220 OR kanal_id=73 OR kanal_id=103 OR kanal_id=148) AND tagname LIKE '%_RoomOperating%'

Bu kanalların(215 503 214 219 ...) altındaki adının içinde '_RoomOperating' geçen etiketlerin polfreq ini 3 olarak set et
--UPDATE public.tags SET polfreq = 3 where (kanal_id=215 OR kanal_id=503 OR kanal_id=214 OR kanal_id=219 OR kanal_id=220 OR kanal_id=73 OR kanal_id=103 OR kanal_id=148) AND tagname LIKE '%_RoomOperating%'

Enable logging for tags with name ending '_adres_0'
update tags set islogging=1,loggingmethod=1,loggingfreq=0 WHERE tagname LIKE '%_adres_0';

Kolon tipini değiştirme
ALTER TABLE logs.tag_log_agrmonth

ALTER COLUMN tag_log_agrmonth_id  TYPE bigint;