Oracle

[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 »

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 »

Restore RMAN backup to another server with different SID

Step 0: Prepare Source database; Target Database : fopsp Target Database Server : bdcorap02 Using ASM storage on Windows Server 2008 R2 Destination database; Auxiliary Database : fopst Auxiliary Database Server : bdcorat02 Using OS file system on Windows Server 2008 R2 Step 1: Take the incremental level 0 backup of the Target database using

Read on »

Oracle RMAN restore until time script

When you did any mistake on your Oracle database such as drop tables or drop databases, you can recover it from the backup copy and archive log files with “RMAN restore until time script” as following;     RMAN> shutdown immediate; database closed database dismounted Oracle instance shut down RMAN> RUN 2> { 3> startup

Read on »

Hash Join

Execution of Hash Join Explanation (1) 개요 JOIN 의 종류는 3가지로 나뉘는데, Sort merge join, Nested loop join, Hash join 이다. 이중 Hash Join (HJ) 은 7.3 부터 사용가능하며 그 주요 기능을 살펴보면 – index 가 여러 level 의 depth 를 가질 때 Sort Merge Join (SMJ) 이나 Nested Loops (NL)보다 좋은 효과를 낸다. – sort 를

Read on »

Oracle database script with Windows PowerShell

If Oracle database is running on Windows environment, PowerShell would be a very good tool for automated scripting for the database such as monitoring or backup script. So the following script would be an example:   Add-Type -Assembly System.Data.OracleClient $connectionString = “Data Source=HOSTNAME:1521/SID; User ID=USERNAME; Password=PASSWORD” $connection = New-Object System.Data.OracleClient.OracleConnection($connectionString) $connection.Open() #$queryString = “SELECT db_link

Read on »

REDO-LESS Operations (NOLOGGING option) in Oracle

REDO-LESS Operations (NOLOGGING option) in Oracle It is possible not to generate redo data at some operations. The examples would be a direct loader, CREATE TABLE AS SELECT(CTAS), and CREATE INDEX with NOLOGGING option. However, even in these cases, a little redo will be generated when the operation changes data dictionary block and when the

Read on »