Posts By: Steve

[SQL] Query to check FK(FOREIGN KEY) Constraint

How to check FK (foreign key) constraint using query in Oracle database Example 1) deptno column on dept table is referenced by deptno pk column on emp table. ### dept(pk : deptno) -> emp(fk : deptno) SQL> alter table dept add constraints pk_deptno primary key (deptno); Table altered. Elapsed: 00:00:00.40 SQL> alter table emp add constraints fk_deptno foreign

Read on »

Oracle RMAN Backup command

Making Whole Database Backups with RMAN RMAN> BACKUP DATABASE; # Uses automatic channels to make backup RMAN> SQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’; # Switches logs and archives all logs Backing up Individual Tablespaces with RMAN RMAN> BACKUP DEVICE TYPE sbt MAXSETSIZE = 10M TABLESPACE users, tools; Backing Up Individual Datafiles and Datafile Copies with

Read on »

How to calculate ROW size

How to calculate ROW size This is the Row Header structure ( cf. “Oracle database 11g Concepts Manual 12.Logical Storage Structures ” ) (a) +-Row Overhead(2Byte) + (b) | Number of columns(1Byte) + (c) | Cluster key ID(if clustered,1Byte) + (d) +- ROWID of chained row piece(if any, 6Byte) 1. If one block can bs in one Row,

Read on »

[Query] Latch information (latchinfo.sql)

This query shows latch information in an Oracle database.   — latchinfo.sql col name for a30 SELECT name, gets, misses,        (misses/decode(gets,0,1,gets))*100 as md, immediate_gets,        immediate_misses,        (immediate_misses/DECODE(immediate_gets,0,1,immediate_gets))*100 as im,        sleeps FROM v$latch WHERE name IN (‘library cache’,                ‘cache buffers chains’,                ‘shared pool’,’cache buffer handles’,                ‘checkpoint queue latch’,                ‘row

Read on »

밴쿠버에서 IT 직장잡기 (5) – 취업

안녕하세요. 블루입니다. 정말 오랜만에 다시 글을 올립니다. 워낙 글재주가 없다보니 글을 쓰기가 겁이 나기도 하고.. ㅋㅋ 전에 올려드린 글에도 말씀드렸지만, 이 글은 순전히 제 경험과 생각을 기준으로 작성된 것이고 밴쿠버에서 IT직종에 취업하시길 희망하시는 분들에게 조금이나마 도움이 될까 하고 써봅니다.   1. 원하는 직종에 지원하기 지난번에 올려드렸던 것처럼 목표를 설정하고 이력서와 커버레터가 잘 마련되면 면접 연습을

Read on »

How to set up AlwaysOn Availability Group in SQL Server 2016

SQL Server 2016 has improved the HA/DR solution – AlwaysOn, and also adds this great feature in Standard Edition with some limitation. AlwaysOn in SQL Server 2016 Standard Edition is very similar with the mirroring feature which was deprecated on SQL Server 2012. This is the some limitation of AlwaysOn in SQL Server 2016 Standard

Read on »

SQL Server TSQL – Take a database offline and online

— Create Test DB CREATE DATABASE [DBName] GO — Take the Database Offline ALTER DATABASE [DBName] SET OFFLINE WITH ROLLBACK IMMEDIATE GO — Take the Database Online ALTER DATABASE [DBName] SET ONLINE GO — Clean up DROP DATABASE [DBName] GO  

Read on »

DBMS_ALERT error issue

Database : Oacle 11.2.0.4 Standard edition OS : Windows Server 2008 R2 64x At this morning, our application got this error message. “Can not change status:ORA-04029: error ORA-7445 occurred when querying Fixed Table/View ORA-06512: at “SYS.DBMS_ALERT”, line 78 ORA-06512: at “SYS.DBMS_ALERT”, line 102 ORA-06512: at “LIMS.UTILITY_PKG”, line 254 ORA-06512: at line 2″   When I

Read on »

SQL Server database engine architecture

The SQLOS was introduced in SQL 2000 and CLR intregration occurred in SQL Server 2005. The database engine has been stable for many releases with few fundamental changes. SQL Server 2016 includes the integration of an R language processor as an external process callable from the SQL Server database engine in much the same way

Read on »