[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

[SQL] Search foreign key relationships and constraint names for each table – SQL Server

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

[SQL] Index fragmentation check – SQL Server

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;

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'));

to Increase dba area size