Oracle Client

Difference between Oracle Client and Oracle Instant Client

Posted on
Here I list their differences including features, pros and cons for you to evaluate which one should be used for your clients: Oracle Client Features Install-based software All-in-one functions General-purpose package Oracle database enterprise edition download page Oracle clients stay with Oracle database software, so there’s no dedicated download page for Oracle clients. Pros One-stop […]

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 contrast, dynamic […]

sqlplus, tnsping or lsnrctl Hangs

Posted on
sqlplus, tnsping or lsnrctl Hangs I can’t cover all situations in this post, but I have some cases that can share with you. Those hanging all point to one problem: Domain name resolution. Does the database server use DNS to do name resolution? Sometimes, DNS traps the name resolution in an unknown and indefinite loop […]

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 error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys Suppose 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 […]

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 […]