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

# はじめに

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にユーザーを作成して接続する手順まとめ
https://qiita.com/sugimount/items/a50376d72c3438383081

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

特定の 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);
```

# パスワードの有効期限を延ばす
https://qiita.com/sugimount/items/3dea100759433762cca8

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

```
SELECT * FROM DBA_DATA_FILES;
```

実行例
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/1897cfd1-b7d7-522a-f590-507fdce76df0.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

http://totech.hateblo.jp/entry/2014/12/29/160402

[https://ひとりでできるもん.com/2018/06/22/cdbpdbの起動停止/](https://xn--w8j8bac3czf5bl7e.com/2018/06/22/cdbpdbの起動停止/)

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