よく使う Oracleのコマンドをまとめておく

SQL*PLUS で Oracle Databaseに接続する
SQL*PLUS の表示幅を確認する
SQL*PLUS の表示幅を変更する
SQL*PLUS のページの行数を設定する
SELECT実行結果の特定列の列幅を変更する
接続しているインスタンス名を確認する
現在の表領域を確認する
表領域が SMALLFILE か BIGFILE を確認する
表領域（SMALL FILE 表領域）を作成する
表領域を BIG FILE で作成する
表領域を BIG FILE で作成する（初期1024MB、自動拡張1024MB、無制限）
表領域を削除する
ユーザー（スキーマー）を追加する【コンテナデータベース有効の場合】
ユーザー（スキーマー）を追加する【コンテナデータベース無効の場合】
ユーザーに付与されているロールを確認する
自分に付与されているロールを確認する
ロールに付与されている権限を確認する
テーブルを作成する
テーブルが格納されている表領域を確認する
ユーザーが所有するテーブルを確認する
ユーザーが所有する表領域を確認する
ユーザーに付与された権限を確認する
オブジェクトに付与された権限を確認する

SQL*PLUS で Oracle Databaseに接続する

cmd＞sqlplus SYS/Password1@orcl as sysdba

C:\Users\Administrator>sqlplus SYS/Password1@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 日 8月 25 17:48:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.



Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。

SQL*PLUS の表示幅を確認する

show linesize
linesize 80

SQL*PLUS の表示幅を変更する

set linesize 150

※コマンドプロンプトで実行する場合は、コマンドプロンプトの幅でも改行されるので注意。

SQL*PLUS のページの行数を設定する

set pagesize 200

SELECT実行結果の特定列の列幅を変更する

書式：column (カラム名) format (書式設定*1) (TRUNCATE*2)
　　*1：
　　　　文字列の場合：a8 とすると文字列で8文字
　　　　数値の場合：0,000.00 カンマ区切り、小数点以下2桁

　　*2：TRUNCATE を指定すると、桁数に収まらない部分は、切り捨てられる。
　　　　指定しないと列内で折り返される。

　　※show parameter の結果には効かないみたい

column TABLESPACE_NAME format a32

フォーマットをクリアする

書式：column (カラム名) clear

接続しているインスタンス名を確認する

select INSTANCE_NAME from V$INSTANCE;

INSTANCE_NAME
--------------------------------
orcl

show parameter INSTANCE_NAME

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 orcl

現在の表領域を確認する

set linesize 150
column TABLESPACE_NAME format a16
column FILE_NAME format a34

select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "MB",STATUS,AUTOEXTENSIBLE,MAXBYTES/1024/1024 "MAX MB" ,INCREMENT_BY,USER_BYTES/1024/1024 "USER MB" ,ONLINE_STATUS
from DBA_DATA_FILES;

表領域が SMALLFILE か BIGFILE を確認する

set linesize 150
column TABLESPACE_NAME format a16

select TABLESPACE_NAME,STATUS,BIGFILE,SHARED
from DBA_TABLESPACES;

表領域（SMALL FILE 表領域）を作成する

表領域 TS001 を SMALL FILE で、100MBの2つのデータファイルで作成する。

create tablespace TS001
    datafile 'D:\ORACLEDATA\ORCL\TS001a.DBF' size 100M,
             'D:\ORACLEDATA\ORCL\TS001b.DBF' size 100M;

表領域が作成されました。

・1つのデータファイルの最大サイズは、約32GBまで。
・1つの表領域に指定できるデータファイルは、1022個まで。

参考
Oracle? Databaseリファレンス - 12c リリース1 (12.1) - A.2 物理データベースの制限
https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-939CB455-783E-458A-A2E8-81172B990FE9.htm

表領域を BIG FILE で作成する

表領域を BIG FILE で作成する（35GB固定）

create bigfile tablespace TS021
    datafile 'D:\ORACLEDATA\ORCL\TS021a.DBF' size 35G;

表領域が作成されました。

表領域を BIG FILE で作成する（初期1024MB、自動拡張1024MB、無制限）

create bigfile tablespace TS022
    datafile 'D:\ORACLEDATA\ORCL\TS022a.DBF'
    size 1024M autoextend on
    next 1024M maxsize unlimited;

表領域が作成されました。

・データファイルの最大サイズは、約32TBまで。
・表領域には、1つのデータファイルのみ指定可能。
・サイズが大きいと、作成には 2～3分かかる。
・createしたタイミングで、HDD上に初期サイズの物理ファイルが出来上がる。

表領域を削除する

書式：drop tablespace (表領域名) [INCLUDING CONTENTS]*1 [AND DATAFILES]*2 [CASCADE CONSTRAINTS]*3 ;

drop tablespace TS021  INCLUDING CONTENTS  AND DATAFILES;

*1：表領域の中にオブジェクトを格納している場合に指定する。
*2：関連する物理データファイルも削除する。
*3：表領域に含まれる表の主キーまたは一意キーを参照する、表領域の外の表からすべての参照整合性制約も削除する場合に指定する。

参考）
Oracle? Database SQL言語リファレンス - 12cリリース1 (12.1) - DROP TABLESPACE
https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_9004.htm

ユーザー（スキーマー）を追加する【コンテナデータベース有効の場合】

※Oracle 12cで、インストール時にコンテナ・データベースを有効にすると、データベースが以下の形に仮想化される。

コンテナ・データベース（CDB） *1
　│　・制御ファイル
　│　・ログファイル
　│
　└ プラガブル・データベース（PDB）*2

*1：Oracle Database インストール時に選択する
*2：インストール時に指定した名前

・ユーザーは、CDBではなく、PDBに作成する。（エラーORA-65096になって作成できない）

Oracle Database に接続する

sqlplus system/Password1@orcl as sysdba

現在の接続状態を確認する

show con_name

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

接続先をPDBに変更する

alter session set container = ORCLPDB;

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

接続先を再度確認する

show con_name

CON_NAME
------------------------------
ORCLPDB

ユーザーを作成する

create user usr01
    identified by Password1
    default tablespace USERS
    temporary tablespace TEMP;

ユーザーが作成されました。

*「ORA-65096: 共通ユーザーまたはロール名が無効です」エラーの場合は、プラガブル・データベースへの接続を行う。

ユーザーに接続権限を付与する

grant connect, resource to usr01;

*1：権限を付与してないと、以下のエラーになります。
　　ERROR:
　　ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

コマンドプロンプトをもう１つ開いて、ログオンしてみる。

dos＞sqlplus usr01/Password1

SQL*Plus: Release 12.2.0.1.0 Production on 日 8月 25 23:25:39 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.



Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL>

ユーザー（スキーマー）を追加する【コンテナデータベース無効の場合】

Oracle Database に接続する

sqlplus system/Password1@orcl as sysdba

現在の接続状態を確認する

show con_name

CON_NAME
------------------------------
orcl

ユーザーを作成する

create user usr01
    identified by Password1
    default tablespace USERS
    temporary tablespace TEMP;


ユーザーが作成されました。

ユーザーに接続権限を付与する

grant connect, resource to usr01;

*1：権限を付与してないと、以下のエラーになります。
　　ERROR:
　　ORA-01017: ユーザー名/パスワードが無効です。ログオンは拒否されました。

コマンドプロンプトをもう１つ開いて、ログオンしてみる。

dos＞sqlplus usr01/Password1

SQL*Plus: Release 12.2.0.1.0 Production on 日 8月 25 23:25:39 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.



Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
SQL>

ユーザーに付与されているロールを確認する

column GRANTEE format a30
column GRANTED_ROLE format a30

select * from DBA_ROLE_PRIVS order by GRANTEE,GRANTED_ROLE;

GRANTEE                        GRANTED_ROLE                   ADMIN_ DELEGA DEFAUL COMMON INHERI
------------------------------ ------------------------------ ------ ------ ------ ------ ------
SYSTEM                         AQ_ADMINISTRATOR_ROLE          YES    NO     YES    YES    YES
SYSTEM                         DBA                            NO     NO     YES    YES    YES
USR01                          CONNECT                        NO     NO     YES    NO     NO
USR01                          RESOURCE                       NO     NO     YES    NO     NO

自分に付与されているロールを確認する

set linesize 150
set pagesize 200

column USERNAME format a30
column GRANTED_ROLE format a30

select * from USER_ROLE_PRIVS;

USERNAME                       GRANTED_ROLE                   ADMIN_ DELEGA DEFAUL OS_GRA COMMON INHERI
------------------------------ ------------------------------ ------ ------ ------ ------ ------ ------
USR01                          CONNECT                        NO     NO     YES    NO     NO     NO
USR01                          RESOURCE                       NO     NO     YES    NO     NO     NO

ロールに付与されている権限を確認する

column ROLE format a30
column PRIVILEGE format a30

select * from ROLE_SYS_PRIVS where ROLE = 'CONNECT';

ROLE                           PRIVILEGE                      ADMIN_ COMMON INHERI
------------------------------ ------------------------------ ------ ------ ------
CONNECT                        SET CONTAINER                  NO     YES    YES
CONNECT                        CREATE SESSION                 NO     YES    YES

テーブルを作成する

create table KAIIN_MASTER (
    ID    CHAR(4),
    NAMAE VARCHAR2(30),
    RANKU NUMBER(1,0),
    constraint PK_KAIIN_MASTER primary key(ID)
) TABLESPACE TS001;

テーブルが格納されている表領域を確認する

column TABLE_NAME format a40
column TABLESPACE_NAME format a20

select TABLE_NAME, TABLESPACE_NAME from USER_TABLES where TABLE_NAME like 'USER%';

ユーザーが所有するテーブルを確認する

column OWNER format a20
column TABLE_NAME format a30

select OWNER, TABLE_NAME from DBA_TABLES where TABLE_NAME like 'KAIIN_MASTER';

OWNER                TABLE_NAME
-------------------- ------------------------------
SYS                  KAIIN_MASTER

ユーザーが所有する表領域を確認する

set linesize 150
set pagesize 200

column GRANTEE format a15
column OWNER format a15
column TABLE_NAME format a15
column GRANTOR format a15
column PRIVILEGE format a20
column TYPE format a10

SELECT * FROM USER_TAB_PRIVS;

GRANTEE         OWNER           TABLE_NAME      GRANTOR         PRIVILEGE            GRANTA HIERAR COMMON TYPE       INHERI
--------------- --------------- --------------- --------------- -------------------- ------ ------ ------ ---------- ------
PUBLIC          SYS             USR01           USR01           INHERIT PRIVILEGES   NO     NO     NO     USER       NO

GRANTEE：権限を付与されたユーザ
GRANTOR：権限を付与したユーザ
OWNER：オブジェクト所有ユーザ
PRIVILEGE：オブジェクト権限の種類

ユーザーに付与された権限を確認する

set linesize 150
set pagesize 200

column GRANTEE format a30
column PRIVILEGE format a30


SELECT * FROM DBA_SYS_PRIVS order by GRANTEE,PRIVILEGE;

オブジェクトに付与された権限を確認する

set linesize 150
set pagesize 200

column GRANTEE format a30
column OWNER format a20
column TABLE_NAME format a40
column GRANTOR format a20
column PRIVILEGE format a20
column TYPE format a10

SELECT * FROM DBA_TAB_PRIVS  where OWNER like 'USR%' order by GRANTEE,OWNER;