Listener

How to Resolve ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Posted on
ORA-12514 Let’s see how we reproduce TNS-12514. First of all, we have to make sure the listener is running well. C:\Users\ed>tnsping compdb TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 – Production on 22-JUL-2014 19:24:04 Copyright (c) 1997, 2013, Oracle.  All rights reserved. Used parameter files: C:\oracle\app\client\ed\product\12.1.0\client_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting […]
PL/SQL

How to Use Bind Variable in Oracle

Posted on
Shareable Cursor Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions. Unshareable Cursors In contrary, dynamic […]
SQL

How to Move Tables by SQL Script

Posted on
I usually move some tables for data reorganization, and I found the procedure is pretty routine:Moving the table.Rebuilding indexes related to this table.Analyzing all segments related to this table.So I wrote a SQL script to handle this automatically, one table at a time.[oracle@test ~]$ cat alter_table_move.sqlset escape on;accept table_owner char prompt ‘Table Owner: ‘;accept table_name […]
Data Pump

How to Disable All Referencing Constraints Before Importing Data

Posted on
Disable Constraints Before you can import with table_exists_action=truncate, you have to disable all referencing constraints. Otherwise, you may get the following error: ORA-02266: unique/primary keys in table referenced by enabled foreign keysSuppose you want to import 3 schemas, HR, SH, OE. Compose disabling statement. SQL> set heading off feedback off pagesize 0 linesize 150 echo off SQL> spool /home/oracle/disable_references.sql […]
User and Schema

Who AM I in Oracle

Posted on
Sometimes, you switched the connection to another schema for more operations and forgot where you are. Here are some ways to know who am I (whoami).If you’re in sqlplus, you check it with the short cut.SQL> show userUSER is “SCOTT”If you’re not in sqlplus, you can also check the information by querying the environmental function […]
SQL*Plus

How to Execute SQL Statements in One CMD Line

Posted on
If your query contains just only one statement, you can do this:C:Usersed>echo select name from v$database; | sqlplus scott/tiger@orclWhen the query is finished, the command will exit sqlplus automatically.If your query contains several statements in a file, you can execute it (e.g. run_check.sql) like the following commands:Method 1: The syntax is supported by sqlplus.C:Usersed>sqlplus sys/password@primdb_scan […]
Oracle Instant Client

How to Install Oracle Instant Client

Posted on
The term “install” may be a little improper for Oracle Instant Client, because the packages are all zipped and portable, there’s no need to actually install on OS-level. Let’s see what the official document says about the steps:Download the appropriate Instant Client packages for your platform. All installations require the Basic or Basic Lite package.That […]
Database

What Symbols are Used in Oracle Database

Posted on
Several symbols that are used in Oracle might represent different meaning in different place.Question mark ?ORACLE_HOME? (a question mark) in a path string, it represents the environmental variable of ORACLE HOME. It’s usually $ORACLE_HOME in UNIX or Linux, %ORACLE_HOME% in WindowsSQL> alter tablespace temp add datafile ‘?/dbs/temp02.dbf’ size 200M;You may also use environment variables directly […]
Listener

TNSPING – 8 Aspects You Should Know

Posted on
tnsping tnsping is a utility provided by Oracle to test the listener is available or not from clients. If there’s any problem in the path to the destination, it will fail with different aspects. Here I list several possible error types below for your reference. “tnsping is not recognized as an internal or external command” […]