サンプル集
Linuxコマンド
用語集
debian メモ
apache2 メモ
MySQL メモ
Oracle メモ
HOME
Oracle メモ

Oracle

SQL*Plus
> sqlplus system/password@NS073

ユーザーsystem
パスワードpassword
ネットサービス名NS073

ネットサービス名は接続文字列という表現が正しいのかも知れません。
tnsnames.oraで定義した文字列を指定します。

１ページの行数を指定

SQL> set pagesize 100

0を指定するとページの区切りがなくなります。

１行の長さを指定

SQL> set linesize 100

列の区切り文字の指定

SQL> set solsep ','

右は時のスペースを削除する指定

SQL> set trimspool on

tnsnames.ora

 ◆記述例
{接続文字列} =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = {ホスト名 or IPアドレス})(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = {サービス名})
  ) )

サービス名はOracleサーバのグローバルデータベース名を指定します。

表ロックの解除
SQL> SELECT SID, SERIAL# FROM V$SESSION
WHERE SID IN (
SELECT SID FROM V$LOCK
WHERE TYPE IN ('TM','TX')
)

このSQLの結果をkillします。

SQL> alter system kill session 'sid, serial#';

SQL文まで確認したい場合、v$sqltextをjoinします。

SQL> SELECT vs.SID, vs.SERIAL#, vt.sql_text FROM V$SESSION
JOIN v$sqltext vt
ON vs.sql_address = vt.address
AND vs.sql_hash_value = vt.hash_value
WHERE vs.SID IN (
SELECT SID FROM V$LOCK
WHERE TYPE IN ('TM','TX')
)

参考：Oracleロック解除

テーブルスペースの確認
SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TS073

6行が選択されました。

テーブルスペースの物理ファイルの確認
SQL> set pages 1000
SQL> set line 100
SQL> column tablespace_name format a15
SQL> column file_name format a50
SQL> select tablespace_name,file_name,bytes from dba_data_files;

TABLESPACE_NAME FILE_NAME                           BYTES
--------------- ------------------------------ ----------
USERS           C:\ORACLE...\USERS.DBF         104857600
SYSAUX          C:\ORACLE...\SYSAUX.DBF        796917760
UNDOTBS1        C:\ORACLE...\UNDOTBS1.DBF      398458880
SYSTEM          C:\ORACLE...\SYSTEM.DBF        387973120
G01             C:\ORACLE...\G01.DBF           2197815296

テーブルスペースの削除

テーブルスペースとテーブルスペースに紐付くデータファイルを削除します。

SQL> DROP TABLESPACE TS073 INCLUDING CONTENTS AND DATAFILES;

ユーザーの一覧

ユーザーの一覧を表示します。

SQL> SELECT username FROM all_users;
USERNAME
------------------------------------------------------------
TS073
SCOTT
:

ユーザーの削除

ユーザーとユーザーに紐付くオブジェクトを削除します。

SQL> DROP USER US073 CASCADE;

systemユーザーのパスワード変更

systemユーザーのパスワードをoracleに変更します。

SQL> ALTER USER system IDENTIFIED BY oracle;

日付の表示形式

TO_CHARを使って日付の書式を指定します。

SQL> SELECT TO_CHAR(entry_dttm,'YYYY-MM-DD HH24:MI:SS') FROM cntnts_mgr
;

TO_CHAR(entry_dttm,
-------------------
2013-06-20 02:26:36
2013-06-20 16:33:39

2行が選択されました。

毎回同じ書式で表示したい場合、NLS_DATE_FORMATで書式を指定すると便利です。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

セッションが変更されました。

Dateカラムの条件指定

TO_DATEを使って日付の書式を指定します。

SQL> SELECT type_code,COUNT(*) FROM trn_data WHERE entry_dttm=TO_DATE('
2014/07/29','YYYY/MM/DD');

typ COU
--- ---
A01   1
A02 234
B01  18

3行が選択されました。

MS-DOSプロンプトの文字コード
> SET NLS_LANG=Japanese_Japan.JA16SJIS
> SET NLS_LANG=AMERICAN_AMERICA.UTF8

データベースのバックアップ(exp)

expコマンドでTS073データベースをTS073.dmpというファイルにバックアップします。

expコマンドは次の場所にありました。

C:\oracle\ora92\bin\EXP.EXE

exp/impはOracleデータベースをインストールするとインストールされるようです。 Oracleクライアントの場合は、管理者を指定するかカスタムで指定するとインストールされるようです。

> exp TS073/TS073@TS073 file=C:\TS073.dmp

テーブルを指定してエクスポート

> exp TS073/TS073@TS073 file=C:\TS073.dmp rows=y tables=(MST_ITEM,MST_N
AME)

データベースの復元(imp)

expコマンドでバックアップをとったTS073.dmpというファイルをインポートします。 ユーザーはexpのときはTS073で、インポートするときはTS055にしました。

> imp TS055/TS055@TS055 file=C:\TS073.dmp fromuser=TS073 touser=TS055

SQLトレースログの採取

トレースログの出力先を確認

> sqlplus sys/oracle@TS01 as sysdba

SQL> show parameter user_dump_dest
NAME           TYPE   VALUE
-------------- ------ --------------------------
user_dump_dest string C:\oracle\admin\TS01\udump

トレースログの出力を有効にする

SQL> alter system set sql_trace=true;
alter system set sql_trace=true
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

エラーが出ました。どうやらインスタンス起動中に sql_traceパラメータは変更できないようです。 scope=spfile を指定して、インスタンス再起動後に有効になるように指定します。

SQL> alter system set sql_trace=true scope=spfile;

System altered.

Windowsのサービスを開き「OracleServiceTS01」を再起動したところ、ログが出力されるようになりました。

トレースログの出力を無効にする

SQL> alter system set sql_trace=false scope=spfile;

System altered.

テーブルの削除(DROP)
SQL> DROP TABLE TS073

リスナーの起動確認
SQL> tnsping localhost

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on
 21-11月-2016 11:03:04

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

パラメータ・ファイルを使用しました:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

エイリアスを解決するためにHOSTNAMEアダプタを使用しました。
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST
=127.0.0.1)(PORT=1521)))に接続の試行中
OK (0ミリ秒)

リスナーの状態確認
SQL> lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 21-11月-
2016 11:08:41

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))に接続中
リスナーのステータス
------------------------
別名                      LISTENER
バージョン                TNSLSNR for 32-bit Windows: Version 11.2.0.2.
0 - Production
開始日                    21-11月-2016 10:53:16
稼働時間                  0 日 0 時間 15 分 25 秒
トレース・レベル          off
セキュリティ              ON: Local OS Authentication
SNMP                      OFF
デフォルト・サービス           XE
パラメータ・ファイル      C:\oraclexe\app\oracle\product\11.2.0\server\
network\admin\listener.ora
ログ・ファイル            C:\oraclexe\app\oracle\diag\tnslsnr\MyPC\list
ener\alert\log.xml
リスニング・エンドポイントのサマリー...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyPC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyPC)(PORT=8080))(Presentat
ion=HTTP)(Session=RAW))
サービスのサマリー...
サービス"CLRExtProc"には、1件のインスタンスがあります。
  インスタンス"CLRExtProc"、状態UNKNOWNには、このサービスに対する1件の
ハンドラがあります...
サービス"PLSExtProc"には、1件のインスタンスがあります。
  インスタンス"PLSExtProc"、状態UNKNOWNには、このサービスに対する1件の
ハンドラがあります...
サービス"XEXDB"には、1件のインスタンスがあります。
  インスタンス"xe"、状態READYには、このサービスに対する1件のハンドラが
あります...
サービス"xe"には、1件のインスタンスがあります。
  インスタンス"xe"、状態READYには、このサービスに対する1件のハンドラが
あります...
コマンドは正常に終了しました。

サービスネームの確認
SQL> lsnrctl services

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 21-11月-
2016 11:13:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))に接続中
サービスのサマリー...
サービス"CLRExtProc"には、1件のインスタンスがあります。
  インスタンス"CLRExtProc"、状態UNKNOWNには、このサービスに対する1件の
ハンドラがあります...
    ハンドラ:
      "DEDICATED" 確立:0 拒否:0
         LOCAL SERVER
サービス"PLSExtProc"には、1件のインスタンスがあります。
  インスタンス"PLSExtProc"、状態UNKNOWNには、このサービスに対する1件の
ハンドラがあります...
    ハンドラ:
      "DEDICATED" 確立:0 拒否:0
         LOCAL SERVER
サービス"XEXDB"には、1件のインスタンスがあります。
  インスタンス"xe"、状態READYには、このサービスに対する1件のハンドラが
あります...
    ハンドラ:
      "D000" 確立:0 拒否:0 現行:0 最大:1022 状態:ready
         DISPATCHER <machine: MyPC, pid: 5208>
         (ADDRESS=(PROTOCOL=tcp)(HOST=MyPC)(PORT=49182))
サービス"xe"には、1件のインスタンスがあります。
  インスタンス"xe"、状態READYには、このサービスに対する1件のハンドラが
あります...
    ハンドラ:
      "DEDICATED" 確立:0 拒否:0 状態:ready
         LOCAL SERVER
コマンドは正常に終了しました。

確立が0以外の出力例です。

サービス"ts012"には、1件のインスタンスがあります。
  インスタンス"ts012"、状態READYには、このサービスに対する1件のハンドラ
があります...
    ハンドラ:
      "DEDICATED" 確立:734 拒否:0 状態:ready
         LOCAL SERVER

Create tableのDDLを確認
set long 10000
set pagesize 200
select dbms_metadata.get_ddl('TABLE', 'tablename') from dual;

インデックスの確認

dba_indexesをテーブル名で検索します。

SQL> select index_name from dba_indexes where table_name='TRN_BILL';

INDEX_NAME
---------------
PK_TRN_BILL
UX1_TRN_BILL
UX1_TRN_BILL1

確認したいindexがテーブルのどのカラムを使っているか検索します。

SQL select column_name from dba_ind_columns where index_name='UX1_TRN_
BILL';

COLUMN_NAME
------------
BRNCH_CODE
BILL_NMBR

インデックスの削除
SQL drop index ux1_trn_bill;

Index dropped.

インデックス名の変更
SQL alter index ux1_trn_bill1 rename to ux1_trn_bill;

Index altered.

▲ PageTop  ■ Home

Copyright (C) 2013 - 2023 ymlib.com