Globalization

ORA-12705: NLSデータファイルにアクセスできないか、無効な環境が指定されましたの解決方法

Posted on
ORA-12705では、NLS設定が正しくないことを示しています。データベースに対して何かを実行する前に、まずそれらを修正する必要があります。 接続前のORA-12705 データベースに接続する前に意図的に不正なNLS_LANGを設定しましょう。これはOracleが使用するために選択する環境変数です。NLS_LANGのフォーマットに関しては、この記事をチェックしてください。 エクスポートインポート文字セットを設定する方法 [oracle@test ~]$ export NLS_LANG=JAPAN_JAPAN.UTF8 [oracle@test ~]$ echo $NLS_LANG JAPAN_JAPAN.UTF8 データベースに接続しようとしました。 [oracle@test ~]$ sqlplus /nolog … SQL> conn hr/hr ERROR: ORA-12705: Cannot access NLS data files or invalid environment specified SQL> conn / as sysdba ERROR: ORA-12705: Cannot access NLS data files or invalid environment specified SQL> exit エラーORA-12705は、両方の接続がデータベースによって拒否されたことを示しています。 どうして? ソリューション 選択する必要があります。1つ目はNLS_LANGの設定を解除すること、もう1つは環境に適した言語を設定することです。 […]
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 […]
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 contrast, dynamic […]
DNS

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 […]
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 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 user USER is “SCOTT” If you’re not in sqlplus, you can also check the information by […]