Go to Qiita Advent Calendar 2024 Top
LoginSignup
20
22

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@sugimount(sugi mount)

Oracle Database に関係するいろいろなコマンドまとめ

Last updated at Posted at 2019-08-31

はじめに

Oracle Database に関係するいろいろなコマンドまとめ
自分が気になったものを随時追記していきます！

SQL*PLUS の接続書式

# OS認証を使用して接続
sqlplus / as sysdba

# Usernameのみ指定
sqlplus username@tnsname

# Password も指定
sqlplus username/password@tnsname

# sysdba で接続
sqlplus sys/password@tnsname as sysdba

# 簡易接続ネーミング・メソッド
sqlplus username/password@//host:port/servicename

SQL*PLUSの出力書式変更

デフォルトだと見にくいので、SQL*PLUS接続後に以下コマンドを発行 (変更しても見にくいけど・・・。VS Code の Extension の方が見やすいかも)

set linesize 1000
set colsep ,
set pagesize 0
set trimspool off
set trimout on

CDBへ接続(SQL*PLUS)

OS認証を使用して接続

sqlplus / as sysdba

tnsnames.ora を使用して接続

tnsnames.ora の作成先は環境に合わせる

vim $ORACLE_HOME/network/admin/tnsnames.ora

編集例

myname=
(DESCRIPTION=
  (LOAD_BALANCE=off) 
  (ADDRESS=
    (PROTOCOL=tcp)  
    (HOST=sugihostname01.priv01.vcn.oraclevcn.com)  
    (PORT=1521)
  ) 
  (CONNECT_DATA=
    (SERVICE_NAME=suginm01_nrt1mv.priv01.vcn.oraclevcn.com) 
    (FAILOVER_MODE=
      (TYPE=select) 
      (METHOD=basic)
    )
  )
)

tnsping で正常に名前解決が出来るか確認

[oracle@sugihostname01 admin]$ tnsping myname 

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 31-AUG-2019 08:38:22

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

Used parameter files: 
/u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (LOAD_BALANCE=off) (ADDRESS= (PROTOCOL=tcp) (HOST=sugihostname01.priv01.vcn.oraclevcn.com) (PORT=1521)) (CONNECT_DATA= 
(SERVICE_NAME=suginm01_nrt1mv.priv01.vcn.oraclevcn.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))
OK (0 msec)

実行例

sqlplus sys/password@myname as sysdba

PDBへ接続(SQL*PLUS)

tnsnames.ora を使用して接続

tnsnames.ora の作成先は環境に合わせる

vim $ORACLE_HOME/network/admin/tnsnames.ora

編集例

  • SERVICE_NAME : pdb のサービス名を指定する
pdbname=
(DESCRIPTION=
  (LOAD_BALANCE=off) 
  (ADDRESS=
    (PROTOCOL=tcp)  
    (HOST=sugihostname01.priv01.vcn.oraclevcn.com)  
    (PORT=1521)
  ) 
  (CONNECT_DATA=
    (SERVICE_NAME=sgpbd01.priv01.vcn.oraclevcn.com) 
    (FAILOVER_MODE=
      (TYPE=select) 
      (METHOD=basic)
    )
  )
)

tnsping で正常に名前解決が出来るか確認

[oracle@sugihostname01 admin]$ tnsping pdbname  

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 31-AUG-2019 08:56:00

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

Used parameter files: 
/u01/app/oracle/product/18.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (LOAD_BALANCE=off) (ADDRESS= (PROTOCOL=tcp) (HOST=sugihostname01.priv01.vcn.oraclevcn.com) (PORT=1521)) (CONNECT_DATA= 
(SERVICE_NAME=sgpbd01.priv01.vcn.oraclevcn.com) (FAILOVER_MODE= (TYPE=select) (METHOD=basic))))
OK (0 msec)

実行例

sqlplus sys/password@pdbname as sysdba

接続先の情報を表示1

show con_name;

CDB に接続している場合
CDB$ROOT と表示される

SQL> show con_name; 

CON_NAME
------------------------------ 
CDB$ROOT

PDB に接続している場合
PDB の名前が表示される

SQL> show con_name; 

CON_NAME
------------------------------
SGPBD01

接続先の情報を表示2

 select
   'DB Name: '  ||Sys_Context('Userenv', 'DB_Name')||
   ' / CDB?: '     ||case
     when Sys_Context('Userenv', 'CDB_Name') is not null then 'YES'
       else  'NO'
       end||
   ' / Auth-ID: '   ||Sys_Context('Userenv', 'Authenticated_Identity')||
   ' / Sessn-User: '||Sys_Context('Userenv', 'Session_User')||
   ' / Container: ' ||Nvl(Sys_Context('Userenv', 'Con_Name'), 'n/a')
   "Who am I?"
   from Dual
   /

実行例

Who am I?
--------------------------------------------------------------------------------
DB Name: CDB1 / CDB?: YES / Auth-ID: SYS / Sessn-User: SYS / Container: CDB$ROOT

CDBの名前を表示

SQL> select instance_name, status from v$instance; 

INSTANCE_NAME    STATUS
---------------- ------------
suginm01         OPEN

PDBの一覧を表示

show pdbs;

実行例

  • PDB$SEED : 新しいPDBの作成に使用できるテンプレート。シードへのオブジェクトの追加や、シード内のオブジェクトの変更は実行できません。CDBには、シードが1つのみあります。
  • SGPBD01 : PDBが表示されている。SGPBD01 というのがPDBの名前
SQL> show pdbs; 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED 
---------- ------------------------------ ---------- ---------- 
         2 PDB$SEED                       READ ONLY  NO
         3 SGPBD01                        READ WRITE NO

PDB へ接続

alter session set container=SGPBD01;

実行例

SQL> alter session set container=SGPBD01; 

Session altered.

SQL> show con_name;  

CON_NAME
------------------------------
SGPBD01
SQL>

備忘録 : エラー時には、PDBの再起動を行うと接続が可能となった

SQL> alter session set container=SGPBD01; 
ERROR: 
ORA-44787: Service cannot be switched into.

PDBを再起動して接続

alter pluggable database SGPBD01 close immediate;
alter pluggable database SGPBD01 open;
alter session set container=SGPBD01;

PDBの起動

全てを起動

alter pluggable database all open;

名前を指定して起動

alter pluggable database TEST122PDB open;

PDBの停止

全てを停止

alter pluggable database all close immediate;

名前を指定して停止

alter pluggable database TEST122PDB close immediate;

リスナーの表示

lsnrctl status

実行例

[oracle@sugihostname01 ~]$ lsnrctl status 

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 30-AUG-2019 15:20:02

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER 
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                18-AUG-2019 08:37:25
Uptime                    12 days 6 hr. 42 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/18.0.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/sugihostname01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))(SDU=65535)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.100.5)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=sugihostname01.priv01.vcn.oraclevcn.com)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+APX" has 1 instance(s).
  Instance "+APX1", status READY, has 1 handler(s) for this service...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "867e3020a52702dee053050011acf8c0.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
Service "8f3a3bbb2413115fe0530564020a2e54.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
Service "sgpbd01.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
Service "suginm01XDB.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 1 handler(s) for this service...
Service "suginm01_CFG.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
Service "suginm01_nrt1mv.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
Service "suginm01_nrt1mv_DGB.priv01.vcn.oraclevcn.com" has 1 instance(s).
  Instance "suginm01", status READY, has 2 handler(s) for this service...
The command completed successfully
[oracle@sugihostname01 ~]$

ユーザーの作成

PDB に接続して実行

書式

CREATE USER <ユーザ名> IDENTIFIED BY <パスワード>

実行例

SQL> CREATE USER sugi IDENTIFIED BY Sug1_Passw0rd_dayo; 

User created.

SQL>

ユーザーに権限付与

例

GRANT CONNECT, RESOURCE TO sugi;

実行例

SQL> GRANT CONNECT, RESOURCE TO sugi; 

Grant succeeded.

SQL>

作成したユーザーで接続

sqlplus sugi/Sug1_Passw0rd_dayo@pdbname

ユーザーを指定して、表領域Quotaの上限を無制限に変更

ALTER USER <username> quota unlimited on <table space name>;

実行例

SQL> ALTER USER sugi quota unlimited on USERS; 

User altered.

SQL>

ユーザーに紐づく PROFILE のパスワード有効期限を無効にする

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

詳細はこちら
https://qiita.com/sugimount/items/3dea100759433762cca8

ユーザー一覧

select username from dba_users;

テーブル一覧

DBA権限の場合

select owner,table_name from dba_tables;

ログインしているユーザーが参照できるすべてのテーブルを表示

select owner,table_name from all_tables;

ログインしているユーザーが OWNER のテーブルを表示

select table_name from USER_TABLES;

テストデータ書き込み

接続

sqlplus / as sysdba

User Create

CREATE USER sugi IDENTIFIED BY Sug1_Passw0rd_dayo; 
GRANT CONNECT, RESOURCE TO sugi;
ALTER USER sugi quota unlimited on USERS; 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

tnsnames 編集

vim $ORACLE_HOME/network/admin/tnsnames.ora

追記

pdbname=
(DESCRIPTION=
  (LOAD_BALANCE=off) 
  (ADDRESS=
    (PROTOCOL=tcp)  
    (HOST=10.0.0.9)  
    (PORT=1521)
  ) 
  (CONNECT_DATA=
    (SERVICE_NAME=pdb01.tokyosubnet01.tokyovcn.oraclevcn.com) 
    (FAILOVER_MODE=
      (TYPE=select) 
      (METHOD=basic)
    )
  )
)

PDB 接続

sqlplus sugi/Sug1_Passw0rd_dayo@pdbname

CREATE TABLE

CREATE TABLE emp 
 (
 empno VARCHAR2(10) NOT NULL,
 empname VARCHAR2(50),
 gender_f NUMBER(1,0)
 ) 
;

INSERT INTO

INSERT INTO emp VALUES (1, 2, 1);
INSERT INTO emp VALUES (2, 3, 1);
INSERT INTO emp VALUES (3, 2, 1);
COMMIT;

SELECT *

SELECT * FROM emp;

Result

SQL> SELECT * FROM emp;

EMPNO      EMPNAME                                              GENDER_F
---------- -------------------------------------------------- ----------
1          2                                                           1
2          3                                                           1
3          2                                                           1

特定のユーザーが実行したSQLの確認

SUGI ユーザーが実行中の SQL の確認

SELECT INST_ID, SID, SERIAL#, SQL_ID, STATUS FROM GV$SESSION WHERE USERNAME='SUGI';

実行例

SQL> SELECT INST_ID, SID, SERIAL#, SQL_ID, STATUS FROM GV$SESSION WHERE USERNAME='SUGI';

   INST_ID        SID    SERIAL# SQL_ID        STATUS
---------- ---------- ---------- ------------- --------
         1         12      52270 0qk4b9c5pzkcs ACTIVE
         1        335      22707               INACTIVE
         1        338      64055               INACTIVE
         1        502      50950               INACTIVE

SQL>

SQL の Text 文を確認

SELECT SQL_ID, PIECE, SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='gjqf6b05g11bq' ORDER BY PIECE;

実行例

SQL> SELECT SQL_ID, PIECE, SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='gjqf6b05g11bq' ORDER BY PIECE;

SQL_ID             PIECE SQL_TEXT
------------- ---------- ----------------------------------------------------------------
gjqf6b05g11bq          0 INSERT INTO EXAMPLES     SELECT       ROWNUM      ,DBMS_RANDOM.S
gjqf6b05g11bq          1 TRING('A', 100)      ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))      ,
gjqf6b05g11bq          2 DBMS_RANDOM.VALUE()      ,TO_DATE('19000101','YYYYMMDD') + FLOOR
gjqf6b05g11bq          3 (DBMS_RANDOM.VALUE(1, 365 * 120))     FROM       (select 0 from
gjqf6b05g11bq          4 all_catalog where rownum <= 10000)      ,(select 0 from all_cata
gjqf6b05g11bq          5 log where rownum <= 100000)

6 rows selected.

SQL>

SQL を中断

実行したいSQLを特定

SQL> SELECT INST_ID, SID, SERIAL#, SQL_ID, STATUS FROM GV$SESSION WHERE USERNAME='SUGI';

   INST_ID        SID    SERIAL# SQL_ID        STATUS
---------- ---------- ---------- ------------- --------
         1         12      52270 0qk4b9c5pzkcs ACTIVE
         1        335      22707               INACTIVE
         1        338      64055               INACTIVE
         1        502      50950               INACTIVE

SQL>

キャンセルコマンド SID, SERIAL#, INST_ID, SQL_ID を指定

ALTER SYSTEM CANCEL SQL '12, 52270, @1, 0qk4b9c5pzkcs';

実行例

SQL> ALTER SYSTEM CANCEL SQL '12, 52270, @1, 0qk4b9c5pzkcs';

System altered.

SQL>

PDBにユーザーを作成して接続する手順まとめ

統計情報の更新状況を確認

特定の Table 名を指定して、統計情報の最終更新日時と行数を確認

SELECT
  TABLE_NAME,
  LAST_ANALYZED,
  NUM_ROWS,
  STATUS
FROM
  USER_TABLES
WHERE
  TABLE_NAME = 'EXAMPLES';

実行例
LAST_ANALYZED が最終更新日で、NUM_ROWS が該当のTableの行数

TABLE_NAME   LAST_ANALYZED NUM_ROWS  STATUS
------------ ------------- --------- --------
EXAMPLES     09-AUG-20     50000000  VALID

統計情報を更新

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'sugi',tabname=>'sales',cascade=>false);

パスワードの有効期限を延ばす

表領域のデータファイルを確認

SELECT * FROM DBA_DATA_FILES;

実行例
image.png

ASM関連

ASMCMD

DBCS で、grid ユーザーにスイッチ

[opc@oradb19ee ~]$ sudo su - grid
Last login: Sat Aug 22 04:01:32 UTC 2020 on pts/0
[grid@oradb19ee ~]$

asmcmd コマンド

p はプロンプトに、現在居る位置を表示

[grid@oradb19ee ~]$ asmcmd -p
ASMCMD [+] >

ls

ASMCMD [+] > ls
DATA/
RECO/
ASMCMD [+] >

-l オプションも使える

ASMCMD [+] > ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      RECO/
ASMCMD [+] >

cd

ASMCMD [+] > cd DATA/
ASMCMD [+DATA] >

ls

ASMCMD [+DATA] > ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            N    ORADB19_NRT1R2/
                                            Y    dbSyshz4emhpq/
PASSWORD  UNPROT  COARSE   AUG 04 08:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.1047545485
PASSWORD  UNPROT  COARSE   AUG 04 08:00:00  N    orapwasm_backup => +DATA/ASM/PASSWORD/pwdasm.257.1047545671
ASMCMD [+DATA] >

cd

ASMCMD [+DATA] > cd ORADB19_NRT1R2
ASMCMD [+DATA/ORADB19_NRT1R2] >

ls

ASMCMD [+DATA/ORADB19_NRT1R2] > ls -l
Type  Redund  Striped  Time  Sys  Name
                             Y    A32A6F1B654641A5E053C105F40A57F6/
                             Y    AC0A9ECE7F2B2FE5E0534700000AA3C9/
                             Y    AC0ABEF76CE358CEE0534700000A887A/
                             Y    DATAFILE/
                             Y    PARAMETERFILE/
                             Y    TEMPFILE/
ASMCMD [+DATA/ORADB19_NRT1R2] >

DATAFILEの確認

ASMCMD [+DATA/ORADB19_NRT1R2] > ls -l DATAFILE
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   AUG 20 22:00:00  Y    SYSAUX.262.1047546433
DATAFILE  UNPROT  COARSE   AUG 12 07:00:00  Y    SYSTEM.261.1047546389
DATAFILE  UNPROT  COARSE   AUG 11 15:00:00  Y    UNDOTBS1.263.1047546449
DATAFILE  UNPROT  COARSE   AUG 11 15:00:00  Y    USERS.274.1047547517
ASMCMD [+DATA/ORADB19_NRT1R2] >

DISK GROUP

lsdg

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   220788                0          220788              0             Y  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304    262144   179764                0          179764              0             N  RECO/
ASMCMD [+] >

lsdsk

ASMCMD [+] > lsdsk
Path
/dev/DATADISK1
/dev/DATADISK2
/dev/DATADISK3
/dev/DATADISK4
/dev/RECODISK1
/dev/RECODISK2
/dev/RECODISK3
/dev/RECODISK4
ASMCMD [+] >

disk 使用量

ASMCMD [+] > lsdsk -k
Total_MB  Free_MB  OS_MB  Name       Failgroup  Site_Name  Site_GUID                         Site_Status  Failgroup_Type  Library  Label  Failgroup_Label  Site_Label  UDID  Product  Redund   Path
   65536    55232  65536  DATA_0002  DATA_0002             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/DATADISK1
   65536    55188  65536  DATA_0001  DATA_0001             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/DATADISK2
   65536    55172  65536  DATA_0000  DATA_0000             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/DATADISK3
   65536    55196  65536  DATA_0003  DATA_0003             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/DATADISK4
   65536    44928  65536  RECODISK1  RECODISK1             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/RECODISK1
   65536    44980  65536  RECODISK2  RECODISK2             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/RECODISK2
   65536    44952  65536  RECODISK3  RECODISK3             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/RECODISK3
   65536    44904  65536  RECODISK4  RECODISK4             00000000000000000000000000000000               REGULAR         System                                                      UNKNOWN  /dev/RECODISK4
ASMCMD [+] >

disk 統計情報

ASMCMD [+] > lsdsk --statistics
  Reads   Write  Read_Errs  Write_Errs    Read_time   Write_Time   Bytes_Read  Bytes_Written  Voting_File  Path
 697701  287520          0           0  1236.936075  3395.014642  27514228736    43560972288            N  /dev/DATADISK1
1134266  262217          0           0  1373.387084  4307.932924  29378458624    43286927360            N  /dev/DATADISK2
 717484  256018          0           0  1772.320747  3875.400057  27906015232    43589595136            Y  /dev/DATADISK3
 693297  276085          0           0  1130.266629  4503.553209  27748327424    43877916672            N  /dev/DATADISK4
  51409  103210          0           0   191.960202   701.403054   8659575296    16087470080            N  /dev/RECODISK1
1243892  426058          0           0   935.777521   997.805004  22569542656    21471966208            N  /dev/RECODISK2
1349442  136308          0           0  1155.724625   940.626733  29913323520    16792078848            N  /dev/RECODISK3
 779994  109334          0           0     712.2695   802.024329  20586233344    16469213696            N  /dev/RECODISK4

参考URL

https://ひとりでできるもん.com/2018/06/22/cdbpdbの起動停止/

GRANT
https://www.shift-the-oracle.com/sql/grant.html

20
22
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
20
22

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?