ORA-12154: TNS:could not resolve service name
First open the tnsnames.ora file and check the settings
eg-
CHECK1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =CHECK2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = CHECK3)
)
)
IN THE ABOVE GIVEN SCRIPT
CHECK1---> Data Source Name u r Connecting from the front end
CHECK2---> Server name to which u r connecting
CHECK3---> Service name/SID of the Data base u r connecting
* - c:\> ping CHECK2 (Host ip or name)
if you cannot ping that means the client server network problem. Else
* - c:\> telnet CHECK2:1521
if you cannot connect to port ,network problem . Else
* - c:\> TNSPING CHECK1
if still the same error
check SQLNET.ORA
Commect other settings and keep only following line and TNSPING.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
If not NTS change to NONE and TNSPING.
If Still not working check whether a Spyware running on your PC.
Run spyware detection tool.
Do you have multiple Oracle installations on your machines like Oracle client and Developer ?
If so you‘ll have to set up the tnsnames.ora on both HOMES.
Or else setup the registry.
Add string value TNS_ADMIN to \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
Set the values of TNS_ADMIN to the folder where the tnsnames.ora you set up resides.
Eg. C:\oracle\ora92\admin
There are different reasons to occur this.
In this artical I‘ll discuss a two special reasons for this.
In this scenario you will get " ORA-12500:TNS listener failed to start a dedicated server process" when you try to connect.
Sometimes you may get this error also
ORA-04030 out of process memory when trying to allocate xxxxx bytes (cursor work he,kllcqas:kllsltba)
Solution.
This is a memory issue and you need to chek RAM.
If you have innough free RAM , check the oracle.exe process in task manager.
Check the memory usage and vitual memory usage of oracle.exe.
in windows 2000 server has a single memory process limit. It is 2GB.
And also oracle old versions has the instance memory limit around 1.8 GB.
If you near one of these limits reduce oracle SGA.So the PGA can use the released
memory to connect database.
To reduce SGA you need to reduce Shared pool in init.ora.
This error occures when PGA don‘t have innough memory .
Oracle date calculations
select trunc(months_between(to_date(‘25-Dec-2004‘,‘dd-Mon-yyyy‘),
to_date(‘01-Jan-1947‘,‘dd-Mon-yyyy‘))/12) ||‘ years ‘||
mod(trunc(months_between(to_date(‘25-Dec-2004‘,‘dd-Mon-yyyy‘),
to_date(‘01-Jan-1947‘,‘dd-Mon-yyyy‘))),12)||‘ months ‘||
trunc(to_date(‘25-Dec-2004‘,‘dd-Mon-yyyy‘) -
add_months(to_date(‘01-Jan-1947‘,‘dd-Mon-yyyy‘),
months_between(to_date(‘25-Dec-2004‘,‘dd-Mon-yyyy‘),
to_date(‘01-Jan-1947‘,‘dd-Mon-yyyy‘)))) ||‘ days‘ "Year_Month_Day"
from dual
Result
57 Year , 11month , 25 days
Updating Base Tables of a Complex View
For the most part, complex views will not enable you to change data in any of the base tables if you haven‘t properly defines foreign key and primary key relationships between the joined tables using appropriate Oracle integrity constraints. let‘s take a lok at what I mean by tis statement. The dictionary view USER_UPDATABLE_COLUMNS can tell you whether the columns in a complex view can be modified. Only columns from the key-preserved table can be modified via the complex view. Columns from the non-key-preserved table cannot be modified via the complex view. A key-preserved table is a table in a complex view whose primary key column is present in the view and whose values are all unique and not NULL in the view.
How to export oracle full database EXP FILE=database.dmp FULL=Y
user name: DBA
password : ***
How to export oracle schema
sql> exp < userid >/< password > owner=SCHEMA1 file=SCHEM1.dmp
more | | |
|
