Go to Qiita Advent Calendar 2024 Top
LoginSignup

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 3 years have passed since last update.

@TakuyaAma(A Takuya)

「Oracle Database に関係するいろいろなコマンドまとめ」の自分メモ用フォーク

Last updated at Posted at 2021-05-18

はじめに

Oracle Database に関係するいろいろなコマンドまとめを自分メモ用にフォーク
限定共有として投稿

SQL*PLUS

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

PDB

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を作成

create pluggable database PDB2 admin user PDB_Admin identified by oracle;

Pluggable database created.

作成したPDBをOpen

alSQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           READ WRITE NO

PDB へ接続

alter session set container=PDB2;

実行例

SQL> alter session set container=PDB2; 

Session altered.

SQL> show con_name;  

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

PDBの起動

全てを起動

alter pluggable database all open;

名前を指定して起動

alter pluggable database TEST122PDB open;

PDBの停止

全てを停止

alter pluggable database all close immediate;

名前を指定して停止

alter pluggable database TEST122PDB close immediate;

CDP/PDBsのTable Spaces表示

COLUMN "Con_Name" FORMAT A10
COLUMN "T'space_Name" FORMAT A12
COLUMN "File_Name" FORMAT A120
SET LINESIZE 220
SET PAGES 9999

with Containers as (
  select PDB_ID Con_ID, PDB_Name Con_Name from DBA_PDBs
  union
  select 1 Con_ID, 'CDB$ROOT' Con_Name from Dual)
select
  Con_ID,
  Con_Name "Con_Name",
  Tablespace_Name "T'space_Name",
  File_Name "File_Name"
from CDB_Data_Files inner join Containers using (Con_ID)
union
select
  Con_ID,
  Con_Name "Con_Name",
  Tablespace_Name "T'space_Name",
  File_Name "File_Name"
from CDB_Temp_Files inner join Containers using (Con_ID)
order by 1, 3
/
    CON_ID Con_Name   T'space_Name File_Name
---------- ---------- ------------ ------------------------------------------------------------------------------------------------------------------------
         1 CDB$ROOT   SYSAUX       /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_j6t69o7d_.dbf
         1 CDB$ROOT   SYSTEM       /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_j6t687vc_.dbf
         1 CDB$ROOT   TEMP         /u01/app/oracle/oradata/CDB1/datafile/o1_mf_temp_j6t6cqr2_.tmp
         1 CDB$ROOT   UNDOTBS1     /u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_j6t6bgl9_.dbf
         1 CDB$ROOT   USERS        /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_j6t6bhx4_.dbf
         4 PDB2       SYSAUX       /u01/app/oracle/oradata/CDB1/C29A547C37815CA2E053D200000AE9F4/datafile/o1_mf_sysaux_jb7bkz7o_.dbf
         4 PDB2       SYSTEM       /u01/app/oracle/oradata/CDB1/C29A547C37815CA2E053D200000AE9F4/datafile/o1_mf_system_jb7bkz7g_.dbf
         4 PDB2       TEMP         /u01/app/oracle/oradata/CDB1/C29A547C37815CA2E053D200000AE9F4/datafile/o1_mf_temp_jb7bkz7t_.dbf
         4 PDB2       UNDOTBS1     /u01/app/oracle/oradata/CDB1/C29A547C37815CA2E053D200000AE9F4/datafile/o1_mf_undotbs1_jb7bkz7r_.dbf
         4 PDB2       USERS        /u01/app/oracle/oradata/CDB1/C29A547C37815CA2E053D200000AE9F4/datafile/o1_mf_users_jb7c5m6q_.dbf

10 rows selected.

リスナーの表示

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 sysdba to pdb_admin;

Grant succeeded.

SQL> create tablespace users datafile size 20M autoextend on next 1M maxsize unlimited segment space management auto;

Tablespace created.

SQL> alter database default tablespace Users;

Database altered.

SQL> grant create table, unlimited tablespace to pdb_admin;

Grant succeeded.

作成したユーザーで接続

sqlplus sugi/Sug1_Passw0rd_dayo@PDB2

ユーザーを指定して、表領域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

PDBのクローン

CDPに接続

SQL> connect sys/oracle@localhost:1523/cdb1 as sysdba
Connected.

PDBをRead Onlyモードに変更

SQL> alter pluggable database PDB2 open read only force;
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB1                      READ WRITE NO
         4 PDB2                      READ ONLY NO

PDB2からPDB3をクローン

SQL> create pluggable database PDB3 from pdb2;

Pluggable database created.

SQL> alter pluggable database pdb3 open force;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB1                      READ WRITE NO
         4 PDB2                      READ ONLY NO
         5 PDB3                      READ WRITE NO
SQL> alter pluggable database PDB2 open read write force;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB1                      READ WRITE NO
         4 PDB2                      READ WRITE NO
         5 PDB3                      READ WRITE NO

Unplug PDB

CDBにSYSDBAとして接続PDBのClose

SQL> connect sys/oracle@localhost:1523/cdb1 as sysdba
Connected.
SQL> alter pluggable database PDB3 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB3
  2  unplug into
  3  '/u01/app/oracle/oradata/CDB1/pdb3.xml';

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB1                      READ WRITE NO
         4 PDB2                      READ WRITE NO
         5 PDB3                      MOUNTED

Remove PDB

SQL> drop pluggable database PDB3 keep datafiles;

Pluggable database dropped.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB1                      READ WRITE NO
         4 PDB2                      READ WRITE NO

Plugin PDB

Unplugしたときと異なるCDBに接続

SQL> connect sys/oracle@localhost:1524/cdb2 as sysdba
Connected.
SQL> COLUMN "Who am I?" FORMAT A120
SQL> select
  2    'DB Name: '  ||Sys_Context('Userenv', 'DB_Name')||
  3    ' / CDB?: '     ||case
  4      when Sys_Context('Userenv', 'CDB_Name') is not null then 'YES'
  5      else 'NO'
  6      end||
  7    ' / Auth-ID: '   ||Sys_Context('Userenv', 'Authenticated_Identity')||
  8    ' / Sessn-User: '||Sys_Context('Userenv', 'Session_User')||
  9    ' / Container: ' ||Nvl(Sys_Context('Userenv', 'Con_Name'), 'n/a')
 10    "Who am I?"
 11  from Dual
 12  /

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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB2                      MOUNTED

compatibilityの確認

SQL> begin
  2    if not
  3      Sys.DBMS_PDB.Check_Plug_Compatibility
  4      ('/u01/app/oracle/oradata/CDB1/pdb3.xml')
  5    then
  6      Raise_Application_Error(-20000, 'Incompatible');
  7    end if;
  8  end;
  9  /

PL/SQL procedure successfully completed.

PDB3をCDB2にPlugin

SQL> create pluggable database PDB3
  2  using '/u01/app/oracle/oradata/CDB1/pdb3.xml'
  3  move;

Pluggable database created.
SQL> alter pluggable database pdb3 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED              READ ONLY NO
         3 PDB2                      MOUNTED
         4 PDB3                      READ WRITE NO

UnplugされたPDBのクローン

PDBをUnplug

SQL> alter pluggable database GOLDPDB
unplug into '/u01/app/oracle/oradata/CDB1/goldpdb.xml';

Unplugされたファイルを用いてクローンを作成

SQL> begin
  2    if not
  3      Sys.DBMS_PDB.Check_Plug_Compatibility
  4  ('/u01/app/oracle/oradata/CDB1/goldpdb.xml')
  5    then
  6      Raise_Application_Error(-20000, 'Incompatible');
  7    end if;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> create pluggable database COPYPDB1 as clone
  2  using '/u01/app/oracle/oradata/CDB1/goldpdb.xml'
  3  storage (maxsize unlimited max_shared_temp_size unlimited)
  4  copy;

Pluggable database created.

PDB Hot Clone

CDB1上のPDB（oe）をCDB2にHot Clone
→ Shellで負荷をかけながら実行

CDB1に接続してoeを作成

SQL> connect sys/oracle@localhost:1523/cdb1 as sysdba
Connected.

SQL> create pluggable database oe admin user soe identified by soe roles=(dba);
Pluggable database created.

SQL> alter pluggable database oe open;
Pluggable database altered.

SQL> alter session set container = oe;
Session altered.

SQL> grant create session, create table to soe;
Grant succeeded.

SQL> alter user soe QUOTA unlimited on system;
User altered.

SOEユーザでoeに接続 > sales_ordersテーブルを作成

SQL> alter user soe QUOTA unlimited on system;
User altered.

SQL> connect soe/soe@localhost:1523/oe
Connected.
SQL> CREATE TABLE sale_orders
  2  (ORDER_ID      number,
  3  ORDER_DATE    date,
  4  CUSTOMER_ID   number);

Table created.

別のターミナルを立ち上げて、sales_ordersにinsertするShellを実行

実行するShell

#!/bin/sh
#
ORACLE_SID=CDB1
ORAENV_ASK=NO
. oraenv

#
echo ""
echo "  NOTE:"
echo "  To break out of this batch"
echo "  job, please issue CTL-C "
echo ""
echo "...sleeping 5 seconds"
echo ""
sleep 5

  sqlplus -S /nolog << EOF
  @truncate_sale_orders.sql
EOF

c=1
while [ $c -le 1000 ]
do
  sqlplus -S /nolog  << EOF
  @batch-orders.sql
  commit;
  @count-sales.sql
  @scn.sql
  @dbname.sql
EOF
sleep 1
(( c++))
done

元のターミナルに戻って作業を継続 > CDB2に接続 > oe_dev PDBをoe@cdb1_linkから作成

SQL> connect sys/oracle@localhost:1524/cdb2 as sysdba
Connected.
SQL> create pluggable database oe_dev from oe@cdb1_link;

Pluggable database created.

SQL> alter pluggable database oe_dev open;

Pluggable database altered.

(参考) CDBのDBLINK設定

SQL> col onwer format A10
SQL> col OWNER format A10
SQL> col DB_LINK format A10
SQL> col USERNAME format A10
SQL> col HOST format A100

SQL> select * from all_db_links;

OWNER      DB_LINK    USERNAME   HOST                                                                                                 CREATED   HID SHA VAL INT
---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- --------- --- --- --- ---
SYS     SYS_HUB            SEEDDATA                                                                                    17-APR-19 NO NO YES NO
SYS     CDB1_LINK SYSTEM (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523)) (CONNECT_DATA = (SERVER = 07-APR-21 NO NO YES NO
                                  DEDICATED) (SERVICE_NAME = cdb1)))

OE_DEVに接続 > sales_ordersの件数をカウント

SQL> connect soe/soe@localhost:1524/oe_dev
Connected.
SQL>
SQL> select count(*) from sale_orders;

  COUNT(*)
----------
       980

oeに接続 > sales_ordersの件数をカウント

SQL> connect soe/soe@localhost:1523/oe
Connected.
SQL> select count(*) from sale_orders;

  COUNT(*)
----------
      3200

Basic

ユーザー一覧

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

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

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

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?