Error ORA-00933 Creating DB Link With Special Characters

Today I get the next error executing a create database link commandd;

ORA-00933: SQL command not properly ended

The syntax is show below

create database link unodos connect to Stevy identified by Pa**word using ‘remotedb.worl.es’;

In this case, the pass have special characters (two asterisks) and Oracle thinks it is sql syntax, you need specify it like string, you can this by user quotes like in the next sample;

create database link unodos connect to Stevy identified by “Pa**word” using ‘remotedb.worl.es’;

 

HTH – Antonio NAVARRO

Advertisements

crsctl stop has vs crsctl stop crs

Last week talking with a coworker, there was the issue of which command to use to stop a clutch, I personally have seen many people use the crsctl stop has, but we really did not know the difference to use stop has or use stop crs, a look at the documentation, always remember, RTFM (Read The Fine Manual) we clarified. Like show below;

use crsctl stop has command for stop a restart (one node OR single-instance)

use crsctl stop crs command for stop a cluster (two or more nodes)

HTH – Antonio NAVARRO

 

Get Stats Preferences

In a previous post I showed the way to get the stats preferences for all database. Today I post the method for all levels (Table, Schema and Database);

TABLE Level:

 
-- Replace  OWNER_USER  by owner user
-- Replace  TABLE_NAME by table name
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'INCREMENTAL'),         
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'GRANULARITY'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'STALE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'ESTIMATE_PERCENT'), 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, TABNAME=> TABLE_NAME, PNAME=>'CASCADE')
FROM DUAL
/

SCHEMA Level:

   
-- Replace OWNER_USER  by owner user             
SELECT 
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'INCREMENTAL'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'GRANULARITY'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'STALE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'ESTIMATE_PERCENT'),
  DBMS_STATS.GET_PREFS(OWNNAME=> OWNER_USER, PNAME=>'CASCADE') 
FROM DUAL
/

DATABASE Level:

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'INCREMENTAL'),
  DBMS_STATS.get_prefs(pname=>'GRANULARITY'),
  DBMS_STATS.get_prefs(pname=>'STALE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'ESTIMATE_PERCENT'),
  DBMS_STATS.get_prefs(pname=>'CASCADE')
FROM DUAL
/

For all cases you can see histograms like show below;

 
SELECT 
  DBMS_STATS.get_prefs(pname=>'METHOD_OPT') method_opt   
FROM DUAL
/

HTH – Antonio NAVARRO