"ORA-00942: table or view does not exist" when try to get data using synonym

This document describes about "ORA-00942: table or view does not exist" issue while trying to get data using synonym

Issues:

SQL> show user

USER is "ADAM"

SQL> create synonym emp for scott.emp;

Synonym created.

SQL> select count(*) from emp;
select count(*) from emp
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

Cause and Solution:

Issue is coming because user ADAM does not have select privilege  on emp table which is under scott schema.

Connect to sys user and grant select privilege on scott.emp table to adam.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> grant select on scott.emp to adam;

Grant succeeded.

Validation:

SQL> show user
USER is "ADAM"

SQL> select count(*) from emp;

  COUNT(*)
----------
14

SQL> 

Thanks for your patience to review the post............

"ORA-01031: insufficient privileges" while creating synonym

This document describes about insufficient privileges issue while creating synonym.

Issue:

SQL> show user
USER is "ADAM"

SQL> create synonym emp for scott.emp;

create synonym emp for scott.emp

*

ERROR at line 1:

ORA-01031: insufficient privileges

Cause and Solution:

Issue is coming because user ADAM does not have create synonym privilege.

Connect to sys and give create synonym privilege to ADAM.

SQL> conn sys as sysdba

Enter password: 

Connected.

SQL> grant create synonym to adam;

Grant succeeded.

SQL> 

Validation:

SQL> show user
USER is "ADAM"

SQL> create synonym emp for scott.emp;

Synonym created.

SQL> select count(*) from emp;

  COUNT(*)
----------
14

SQL>

Thanks for your patience to view the post.........

What is the difference between public and private database links? How to create them?

This document describes what is Public and Private database links and how to create them.

Difference between Public and Private database link:

Please Click Here to know about dblinks.

Create Private database link:

Assumption: We have two databases GOLD and SILVER in two different hosts. SILVER has table called "TARGET". We want to access TARGET table from GOLD database by using database link.

Syntax: create database link <database link name> connect to remote_username identified by remote user password using 'tns_service_name';


SQL> show parameter db_name

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name      string GOLD

SQL> show user

USER is "ADAM"

SQL> create database link gold_to_silver connect to chris identified by welcome using 'SILVER';

Database link created.

SQL> select * from target@gold_to_silver;

       SNO    NAME       SALARY

----------     ----------      ----------

       101     KUMAR      5000

Verification: Connect to target database and verify target table.

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string SILVER

SQL> conn chris/welcome

Connected.

SQL> select * from target;

       SNO NAME       SALARY

---------- ---------- ----------

       101 KUMAR      5000

Create Public database link:

Syntax: 

create public database link < database link name> connect to remote user identified by remote user password using 'tns_service_name';

SQL> show user
USER is "ADAM"

SQL> create  public database link GOLD_TO_SILVER_PUBLIC connect to chris identified by welcome using 'SILVER';

Database link created.

SQL> select * from target@GOLD_TO_SILVER_PUBLIC;

       SNO   NAME       SALARY
----------    ----------       ----------
       101    KUMAR      5000

SQL> show user
USER is "SYS"

SQL> select * from target@GOLD_TO_SILVER_PUBLIC;

       SNO     NAME       SALARY

----------      ----------       ----------

       101      KUMAR      5000

Thanks for your patience to view this post...........

ORA-01031: insufficient privileges while creating database link

This document describes about "ORA-01031" issue when create private database link.


Issue: Getting below error while creating private database link.

SQL> conn adam/welcome
Connected.

SQL> show user
USER is "ADAM"

SQL> create database link gold_to_silver connect to chris identified by welcome using 'SILVER';
create database link gold_to_silver connect to chris identified by welcome using 'SILVER'
                     *
ERROR at line 1:
ORA-01031: insufficient privileges


Cause and Solution:

User ADAM does not have "CREATE DATABASE LINK" privilege to create private database link.

Connect to database as sys and grant "CREATE DATABASE LINK" to user ADAM.

SQL> conn sys as sysdba

Enter password: 

Connected.

SQL> show user

USER is "SYS"

SQL> grant create database link to adam;

Grant succeeded.

Validation:

SQL> conn adam/welcome

Connected.

SQL> show user

USER is "ADAM"

SQL> create database link gold_to_silver connect to chris identified by welcome using 'SILVER';

Database link created.

SQL> 

Thanks for your patience to view this post............

ORA-01031: insufficient privileges while creating table in database

This document describes about "ORA-01031" issue.

Issue:

SQL> show user
USER is "CHRIS"

SQL> create table target(SNO Number(10), Name Varchar2(10), Salary Varchar2(10));
create table target(SNO Number(10), Name Varchar2(10), Salary Varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>


Cause and Solution:

User Chris does not have "CREATE TABLE" privilege to create table. Login as sys and grant "CREATE TABLE" privilege to user CHRIS.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL> show user

USER is "SYS"

SQL> grant create table to chris;

Grant succeeded.

Validation:

SQL> conn chris/welcome

Connected.

SQL> show user

USER is "CHRIS"

SQL> create table target(SNO Number(10), Name Varchar2(10), Salary Varchar2(10));

Table created.

SQL> 

Thanks for your patience to view this post............

ORA-01045: user lacks CREATE SESSION privilege; logon denied while creating user in database

This document describes about "ORA-01045" issue.

Issue:

SQL> conn chris/welcome
ERROR:
ORA-01045: user CHRIS lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

Cause and Solution:

User CHRIS does have "CREATE SESSION" privilege to connect database.

connect to database as sys and grant "CREATE SESSION" privilege to CHRIS.

SQL> conn sys as sysdba

Enter password: 

Connected.

SQL> show user

USER is "SYS"

SQL> grant create session to chris;

Grant succeeded.

Validation:

SQL> conn chris/welcome

Connected.

SQL> show user

USER is "CHRIS"

Thanks for your patience to view this post.........

What is database link and explain.....

This document describes us what is database link and how to create it between two databases.

What is database link?

A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B,
but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.

TYPES of database links:

1. Public database link
2. Private  database link
3. Global database link

Public database link:

It is a database-wide link. All users and PL/SQL subprograms in the database can use the link to access database objects in the corresponding remote database.

Private database link:

It is a database link under specific schema of the local database. Only the owner of a private database link or PL/SQL subprograms in the schema can use this link to access database objects in the corresponding remote database.

Global database link:

It is a network-wide link. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database.




Thanks for your patience to view this post.......

ORA-01950: no privileges on tablespace 'USERS'

This post describes us how to troubleshoot "ORA-01950" issue.

Issue:

SQL> show user

USER is "ADAM"

SQL> insert into source values(100,'VIJAY','BANGALORE');

insert into source values(100,'VIJAY','BANGALORE')

            *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Solution:

User ADAM does have privileges on USERS tablespace. Need to give grant "QUOTA UNLIMITED" to user ADAM to overcome this issue.

Connect as sys to database and give the concern grant.

SQL> conn sys as sysdba

Enter password: 

Connected.

SQL> show user

USER is "SYS"

SQL> alter user adam quota unlimited on users;

User altered.

Validation:

SQL> conn adam/welcome

Connected.

SQL> insert into source values(100,'VIJAY','BANGALORE');

1 row created.

SQL> commit;

Commit complete.

Thanks for your patience to view this post...........

How to create user in database.

This document describes how to create user in oracle database.....

Create  user:

[oracle@server1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 22:22:05 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user adam identified by welcome;

User created.


Connect to user:

SQL> conn adam/welcome
ERROR:
ORA-01045: user ADAM lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.


We need to give create session privilege to user to overcome this issue.

SQL> grant create session to adam;

Grant succeeded.

SQL> conn adam/welcome
Connected.

SQL> show user
USER is "ADAM"

SQL>


Thanks for your patience to view this post........

TNS-12543: TNS:destination host unreachable - tnsping failing for remote database

This document describes the solution for "TNS-12543: TNS:destination host unreachable"

This is the most common known issue we face when we do tnsping for remote database.

Issue Details:

[oracle@server2 admin]$ tnsping gold

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 03-JUN-2018 20:07:01

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOLD.apps.com)))
TNS-12543: TNS:destination host unreachable


Findings and Solution:

Find whether both the hosts are reachable each other

From Server1:

[oracle@server1 admin]$ ping server2
PING server2.apps.com (192.168.1.120) 56(84) bytes of data.
64 bytes from server2.apps.com (192.168.1.120): icmp_seq=1 ttl=64 time=0.468 ms
64 bytes from server2.apps.com (192.168.1.120): icmp_seq=2 ttl=64 time=0.857 ms
64 bytes from server2.apps.com (192.168.1.120): icmp_seq=3 ttl=64 time=0.432 ms
64 bytes from server2.apps.com (192.168.1.120): icmp_seq=4 ttl=64 time=0.575 ms
^C
--- server2.apps.com ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3505ms
rtt min/avg/max/mdev = 0.432/0.583/0.857/0.166 ms


From Sever2:

[oracle@server2 admin]$ ping server1
PING server1.apps.com (192.168.1.110) 56(84) bytes of data.
64 bytes from server1.apps.com (192.168.1.110): icmp_seq=1 ttl=64 time=0.331 ms
64 bytes from server1.apps.com (192.168.1.110): icmp_seq=2 ttl=64 time=0.702 ms
64 bytes from server1.apps.com (192.168.1.110): icmp_seq=3 ttl=64 time=0.768 ms
64 bytes from server1.apps.com (192.168.1.110): icmp_seq=4 ttl=64 time=0.786 ms
64 bytes from server1.apps.com (192.168.1.110): icmp_seq=5 ttl=64 time=0.896 ms
^C
--- server1.apps.com ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4023ms
rtt min/avg/max/mdev = 0.331/0.696/0.896/0.195 ms

We need to disable firewall setting(IP tables) if the servers are reachable each other. 

[root@server1 ~]# service iptables stop

iptables: Setting chains to policy ACCEPT: filter          [  OK  ]

iptables: Flushing firewall rules:                         [  OK  ]

iptables: Unloading modules:                               [  OK  ]

Note: We need to stop IP tables in both the servers.

Successfully able to do tnsping after disable firewall.

[oracle@server2 admin]$ tnsping gold

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 03-JUN-2018 21:42:29

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.110)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOLD.apps.com)))

OK (10 msec)

Thanks for your patience to view this post...........

How to add datafile in oracle database

This document describe how to add a datafile to Non-ASM database.

Step 1:

Find the name of the database.

SQL> select name,open_mode from v$database;

NAME              OPEN_MODE
---------             --------------------
PRIMARY        READ WRITE


Find the tablespace name and datafile location to which we need to add datafile.


SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like  'TEST';

TABLESPACE_NAME                FILE_NAME
------------------------------           -------------------------------------------------------
TEST                      /u02/oradata/PRIMARY/test01.dbf


Step 2: Add datafile to the tablespace

SQL> alter tablespace TEST add datafile '/u02/oradata/PRIMARY/test02.dbf' size 50m autoextend on next 10m maxsize unlimited;

Tablespace altered.

SQL>

Verify the added datafile

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like  'TEST';

TABLESPACE_NAME                FILE_NAME
------------------------------           -------------------------------------------------------
TEST                      /u02/oradata/PRIMARY/test01.dbf
TEST                     /u02/oradata/PRIMARY/test02.dbf

SQL>

Thanks for your patience to view this post............

How to create tablespace in oracle database

This document describes us how to create tablespace in oracle database.

Step 1:

Find the name of the database

SQL> select name,open_mode from v$database;

NAME               OPEN_MODE
---------              --------------------
PRIMARY         READ WRITE

Find the datafiles location

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME              FILE_NAME
------------------------------             -------------------------------------------------------
SYSTEM                                  /u02/oradata/PRIMARY/system01.dbf
SYSAUX                                 /u02/oradata/PRIMARY/sysaux01.dbf
EXAMPLE                              /u02/oradata/PRIMARY/example01.dbf
USERS                                    /u02/oradata/PRIMARY/users01.dbf
UNDOTBS1                            /u02/oradata/PRIMARY/undotbs01.dbf


Step 2: Create new tablespace using above datafile location

SQL> create tablespace TEST datafile '/u02/oradata/PRIMARY/test01.dbf' size 50m autoextend on next  10m maxsize unlimited;

Tablespace created.

SQL>

Verify the  newly created tablespace.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME         FILE_NAME
------------------------------        -------------------------------------------------------
SYSTEM                              /u02/oradata/PRIMARY/system01.dbf
SYSAUX                             /u02/oradata/PRIMARY/sysaux01.dbf
EXAMPLE                          /u02/oradata/PRIMARY/example01.dbf
USERS                                /u02/oradata/PRIMARY/users01.dbf
TEST                                   /u02/oradata/PRIMARY/test01.dbf
UNDOTBS1                         /u02/oradata/PRIMARY/undotbs01.dbf

6 rows selected.

SQL>

Thanks for your patience to view this post.......