Tag Archives: Oracle

[Oracle] Drop database script

— Drop database
SQL> startup nomount restrict
ORACLE instance started.
Total System Global Area 2684354560 bytes
Fixed Size                  2098688 bytes
Variable Size            1090521600 bytes
Database Buffers         1577058304 bytes
Redo Buffers               14675968 bytes
SQL> alter database mount;
Database altered.
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>​

[Oracle] Global name setting when db links doesn’t work without “.world”

This article is showing how to resolve issue when query has an issue during its execution with database link.

When the query works with “.world” on the db link name, but it doesn’t work without “.world”.

Example) This has been tested on Oracle Database 12.2

17:14:27 TESTDB> select 1 from dual@SIMPLE;
select 1 from dual@SIMPLE
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

Elapsed: 00:00:00.00
17:14:40 TESTDB> select 1 from dual@SIMPLE.world;
1
———-
1

Then, the global name setting should be checked :

17:20:58 TESTDB> select * from global_name;
GLOBAL_NAME
——————————
TESTDB

There is no “.world” at the end of the global_name. So just this simply can be updated with the following command. (No database bounce required)

17:21:58 TESTDB> ALTER DATABASE RENAME GLOBAL_NAME TO TESTDB.WORLD;
17:21:28 TESTDB> select * from global_name;
GLOBAL_NAME
——————————
TESTDB.WORLD

17:21:28 TESTDB> select 1 from dual@SIMPLE;
1
———-
1

FYI, this is not related with db domain name setting. If db_domain is changed, then you will need to change lots of things such as db service names, listeners, and so on. Please don’t touch db_domain name for only this “.world” on db link name issue.

17:22:03 TESTDB> show parameter domain
NAME TYPE VALUE
———————————— ———- ——————————
db_domain string

Oracle SQLT utility

SQLT can be downloaded from Note 215187.1 on My Oracle Support for those that have a valid Oracle Support contract. The utility comes as a simple zip file. Before SQLT can be used, it must be installed in the database. Installation will create two schemas, sqltxplain and sqltxadmin. The utility was unzipped to the directory /home/oracle/sqlt and the installation is started below. The installation should be performed as a sysdba user.

SQL> connect / as sysdba
SQL> @/home/oracle/sqlt/install/sqcreate.sql

PL/SQL procedure successfully completed.

 

Installing SQLT

SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:

  1. Uninstall a prior version (optional).

This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql
  1. Execute installation script sqlt/install/sqcreate.sql connected as SYS.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

 

During the installation you will be asked to enter values for these parameters:

  1. Optional Connect Identifier (mandatory when installing in a Pluggable Database)

In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the “Enter” key. Entering nothing is the most common setup.

The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.

  1. SQLTXPLAIN password.

Case sensitive in most systems.

  1. SQLTXPLAIN Default Tablespace.

Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

  1. SQLTXPLAIN Temporary Tablespace.

Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

  1. Optional Application User.

This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won’t be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

  1. Licensed Oracle Pack. (T, D or N)

You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

 

If a silent installation is desired, there are three options to pass all 6 installation parameters:

  1. In a file.

Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sqlinstead of sqlt/install/sqcreate.sql.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdefparams.sql
SQL> START sqcsilent.sql

  1. In-line.

Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcsilent2.sql ” sqltxplain USERS TEMP ” T

  1. Internal installation at Oracle.

Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sqlfollowed by sqlt/install/sqcsilent.sql.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcinternal.sql

If you need further help with install issues, you can get help in the following community thread: SQLTXPLAIN: SQLT Installation Issues

Reference : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=251255531953961&parent=DOCUMENT&sourceId=215187.1&id=1614107.1&_afrWindowMode=0&_adf.ctrl-state=18te3heobc_114

[RMAN] Check Backup status (rman)

This script will report on all backups – full, incremental and archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

[Oracle] How to enable/disable a scheduled job?

Using the package DBMS_SCHEDULER one can enable/disable jobs.

To disable job: This disables the job from running

SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB';

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB FALSE

To enable job:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);

PL/SQL procedure successfully completed.

check job status

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB';

JOB_NAME ENABL

————————- —–

GATHER_STATS_JOB TRUE

Rule Base Optimizer

질의 최적화(query optimization)에서 RBO(Rule Base Optimizer)는 정해진 랭킹(ranking)에 의해 플랜을 결정한다.

같은 랭킹이라면 Where 절의 뒤부터, From절 뒤의 객체가 우선 순위를 갖는다. 한 객체(예 : 테이 블)에서 같은

랭킹의 인덱스가 있다면 가장 최근에 만들어진 인덱스를 사 용한다. 이는 CBO(Cost Base Optimizer)에서도 같이

적용되는 사항이다.

다분히RBO는 개발자들이 프로그래밍 단계에서 SQL 문장 구조의 인위적 인 조정 등으로 인덱스를 사용 못하게 하는

등 개발자가 코딩에 신경을 많 이 써야 하는 문제점이 있다. 또한RBO는 해당 질의에 대한 테이블의 인덱스가

존재한다면 전체 90% 이상의 대상이어도 인덱스를 선택한다는 것이 다. 즉, RBO는 무조건 다음과 같은 미리 정해진

룰을 기준으로 플랜을 결 정하게 된다. 1992년 Oracle 7에서 CBO가 지원되면서 CBO는 계속적인 신기능의 적용으로

발전해 온 반면, RBO는 더 이상의 기능 향상은 없으며, 향후는CBO만 지원될 계획이다. 그러므로RBO에 더 이상의

미련을 갖지 말기 바라며, CBO의 훌륭한 기능들을 적극 활용하길 바란다.

다음은RBO의 랭킹을 정리한 것이다.

Path 1 : Single Row by Rowid

Path 2 : Single Row by Cluster Join

Path 3 : Single Row by Hash Cluster Key with Unique or Primary Key

Path 4 : Single Row by Unique or Primary Key

Path 5 : Clustered Join

Path 6 : Hash Cluster Key

Path 7 : Indexed Cluster Key

Path 8 : Composite Index

Path 9 : Single-Column Indexes

Path 10 : Bounded Range Search on Indexed Columns

Path 11 : Unbounded Range Search on Indexed Columns

Path 12 : Sort-Merge Join

Path 13 : MAX or MIN of Indexed Column

Path 14 : ORDER BY on Indexed Column

Path 15 : Full Table Scan

** last_ddl_time 참조

JPPD : Join Predictive Pushdown

CREATE OR REPLACE TYPE SALES_CUST_TYPEAS OBJECT
(prod_cnt NUMBER(5),
 channel_cnt NUMBER(2),
 tot_amt NUMBER(15,2));
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       s.cust_id, s.cust_first_name, s.cust_last_name,
       s.sales_cust.prod_cnt,  -- the Alias was used here
       s.sales_cust.channel_cnt,
       s.sales_cust.tot_amt
  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */
               c.cust_id, c.cust_first_name, c.cust_last_name,
               (SELECT sales_cust_type -- the type name should be used as it is
                          (COUNT (DISTINCT s.prod_id),
                           COUNT (DISTINCT s.channel_id),
                           SUM (s.amount_sold)
                          )
                  FROM sales s
                 WHERE s.cust_id = c.cust_id
) AS sales_cust -- this Alias is being used on the main query block.
          FROM customers c
         WHERE c.cust_year_of_birth= 1987
           AND ROWNUM   ;

The SQL above shows the similar effect when JPPD is being used. So it is same as a lateral view has been created.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

[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 key (deptno)
references dept
/

 

fk_const_to.sql

DOC
file: fk_const.sql
input : child
output : parent
EX) dept(pk : deptno) -> emp(fk : deptno)
if the input is emp, then the output is dept
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated ,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and b.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

 

Result :

Enter value for table_name: emp
old 16: and b.table_name = upper('&table_name')
new 16: and b.table_name = upper('emp')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

fk_const_from.sql

DOC
file: fk_const_from.sql
input : parent
output : child
dept(pk : deptno) -> emp(fk : deptno)
if the input is dept, then the output is emp
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and a.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

Enter value for table_name: dept
old 16: and a.table_name = upper('&table_name')
new 16: and a.table_name = upper('dept')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

 

 

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 RMAN

RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY '/tmp/system01.dbf';

Backing Up Datafile Copies

RMAN> BACKUP DEVICE TYPE sbt DATAFILECOPY '/tmp/system01.dbf';

Including the Current Control File in a Backup of Other Files

RMAN> BACKUP DEVICE TYPE sbt TABLESPACE users INCLUDE CURRENT CONTROLFILE;

Backing Up the Current Control File Manually

RMAN> BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup;

Backing Up a Control File Copy

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY '/tmp/control01.ctl';

Backing Up Server Parameter Files with RMAN

RMAN> BACKUP DEVICE TYPE sbt SPFILE;

Backing Up Archived Redo Logs with RMAN

RMAN> BACKUP ARCHIVELOG ALL;

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

RMAN> BACKUP ARCHIVELOG FROM TIME "to_date('20161121 124000','YYYYMMDD HH24MISS')" UNTIL TIME "to_date('20161121 140000','YYYYMMDD HH24MISS')" DELETE INPUT TAG 'ARCHIVELOG_LOG_BACKUP';

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)

row_header_format

1. If one block can bs in one Row,

57 byte + 23* INITRANS + 4 (table directory) + 2* (the number of ROWs for one block) is allocated.

The overhead for each ROW is 3 bytes (a+b).
And each column’s overhead is 1 byte if it is less than 250 bytes, or it is 3 bytes if it is more than 250 bytes) (e)
But, if null columns are located at the very end, the overhead of these columns is saved. (*)

Factor of Tuning

ex1)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,null,null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead(1byte) included) = 5 bytes

; if there are 10 rows of this data, the total size will be 50 bytes

ex2)

A varchar2(10), B number, C char(10) ,D char(10), E varchar2(10), F char(10)
and
Data : ('a',null,null,'d',null,null)

then,

3B(a+b) +
2B(for data 'a', column overhead included) +
2B(for two null columns ) +
11B(for data 'd',column overhead(1byte) included) = 18 bytes

ex3)

A varchar2(10), B number
and
Data : ('a',1000000000)

then,

3B(a+b) +
2B(for data 'a',column overhead included) +
3B(for data 1000000000,column overhead(1byte) included) = 8 bytes

; number 1000000000 is converted to 1*E9 internally (integer, decimal, float) -> 2bytes
; number 1000000001 is internally (?) -> 6 bytes

If lots of data is in it, x byte will be increased for the chain of the next block, and if we calculate this with the avg_row_len of user_tables, this size will be much larger bytes than the average row size.
=> (available space / avg_row_len)
* Available space = (block size - block header) - (block size - block header) * 0.1 (if pctfree is 10%)......

 

2. If one block cannot be in one Row,

Chain will be happened. So ROWID(6 bytes) as the overhead of this will be added in order to point chained block on the row header.
And the length indicator(if <= 250 bytes, then 1 bytes. if > 250 bytes, then 3 bytes) of the column which is linked with the chained block will be added will be added.

ex4)

SQL> create table Tbl1 ( c1 varchar2(2000)) pctfree 0;
SQL> Data insert ..

(a) (b) (d) (e)
(e) (Total Size)

; 1800 characters inserted(1 block) => 2 + 1 + 3 + 1800 = 1506 bytes

; 1900 characters inserted(2 block) => 2 + 1 + 6 + 3 + about 1850 + 1 + 50 = 1913 bytes

; even if pctfree = 50, at least one recorder should be inserted.


So,

SQL> analyze table Tbl1 compute statistics;

SQL> select table_name, num_rows, blocks, avg_row_len 

from user_tables;

SQL> select vsize(c1) from Tbl1;

 

If you have any question, please leaver your comment.