DatabaseNotes

From Mikrodev Documentation
(Redirected from DatabaseLearnedLessons)
Jump to navigation Jump to search

Contents

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;