sqlplusとかoracleで使うsqlとか

2022.11.21

スプール

sqlplus /nolog
spool hoge.txt
spool off

テーブル削除

drop table GVIS_EMP
drop table GVIS_EMPLOY

テーブル作成

CREATE TABLE GVIS_EMP
  (
    no    VARCHAR2(6) NOT NULL,
    name  VARCHAR2(60) ,
    age   NUMBER(3,0),
    created DATE DEFAULT SYSDATE,
    modified DATE,
    CONSTRAINT pk1 PRIMARY KEY(no)
  ) ;

テーブル一覧

select table_name from user_tables order by 1;

テスト用項目挿入

insert into GVIS_EMP (no,name,age) values ('0001','dummy1',31) ;
insert into GVIS_EMP (no,name,age) values ('0002','dummy2',32) ;
insert into GVIS_EMP (no,name,age) values ('0003','dummy3',33) ;
insert into GVIS_EMP (no,name,age) values ('0004','dummy4',34) ;
insert into GVIS_EMP (no,name,age) values ('0005','dummy5',35) ;

テーブル内容表示

select * from GVIS_EMP ;

テーブル定義参照

SELECT 
  DBMS_METADATA.GET_DDL('TABLE', USER_TABLES.TABLE_NAME ),
  DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', USER_TABLES.TABLE_NAME )
FROM USER_TABLES ;

プライマリキー削除

ALTER TABLE GVIS_EMP DROP CONSTRAINT pk1 ;

プライマリキー追加

ALTER TABLE GVIS_EMP ADD CONSTRAINT pk1 PRIMARY KEY (no,name) ;

カラム追加と全レコードへ初期値設定

ALTER TABLE GVIS_EMP ADD (ITEM_FOO char(6)) ;
UPDATE GVIS_EMP SET ITEM_FOO = 'FOOFOO' ;

カラム名変更

ALTER TABLE GVIS_EMP RENAME column ITEM_FOO to ITEM_BOO ;

カラム属性変更

ALTER TABLE GVIS_EMP MODIFY ITEM_BOO char(10) ;

テーブルのコピー

CREATE TABLE GVIS_EMPLOY as select * from GVIS_EMP ;

テーブルの内容比較

差異があると違いのある箇所がselect結果に出力される。

select * from GVIS_EMP    minus select * from GVIS_EMPLOY ;
select * from GVIS_EMPLOY minus select * from GVIS_EMP ;

テーブルのディスク使用率の参照

select 
  a.tablespace_name "(表領域全体)"
 ,a.file_name
 ,sum(a.bytes)/1024/1024 bytes_mb
 ,round(sum(bytes-nvl(b.c3,0))/1024/1024,2) "使用量MB"
 ,round(sum(nvl(b.c3,0))/1024/1024,2) "空き容量MB"
 ,round((sum(bytes-nvl(b.c3,0))/1024/1024)/(sum(a.bytes)/1024/1024)*100,2) "全体使用量(%)"
from dba_data_files a,
  (select tablespace_name c1, file_id c2, sum(bytes) c3
   from dba_free_space
   group by tablespace_name, file_id
  ) b
where a.tablespace_name=b.c1(+)
  and a.file_id=b.c2(+)
group by tablespace_name,a.file_name

view削除

drop view GVIS_VIEW_EMP

view作成

create view GVIS_VIEW_EMP as (
  select
     no   "社員番号"
    ,name "氏名"
    ,age  "年齢"
  from GVIS_EMP
) ;

view一覧

select owner,object_type,object_name from dba_objects
where object_type = 'VIEW' order by 1,2,3 ;

viewでinvalidがある一覧

select owner,object_type,object_name from dba_objects
where object_type = 'VIEW' and STATUS <> 'VALID'
order by 1,2,3 ;

view定義参照

select TEXT from user_views ;

viewをリコンパイル

alter view GVIS_VIEW_EMP compile;

権限一覧

select * from user_tab_privs ;
select * from all_tab_privs ;

viewから参照しているテーブル一覧

select 
  OWNER, NAME, TYPE,
  REFERENCED_OWNER,
  REFERENCED_NAME,
  REFERENCED_TYPE
from dbs_dependencies
order by 1,2,3

権限付与

grant select on gvis.ほげ to user ;

viewと権限一覧

select grantee, owner, table_name, grantor, privilege 
from user_tab_privs 
where type = 'VIEW' order by table_name,grantee ;

select table_name,grantee,privilege from all_tab_privs
 where table_schema = 'gvis'
   and table_name in (select view_name from user_views )
order by talbe_name, grantee ;

ユーザIDのパスワード有効期限確認

set linesize 200
set tab off
col profile format a32
col limit   format a32
select * from dba_profiles where resource_name = 'PASSWORD_LIFE_TIME';
select s.username, t.resource_name, t.limit
  from dba_users s,dba_profiles t
where s.profile = t.profile and s.username = 'xxx'

ユーザパスワード変更

alter user xxx identified by yyy ;

パスワード有効期限設定

alter profile userprofile limit password_life_time 180 ;
alter profile userprofile limit password_max unlimited ;

sqlplusでoracle21のpdb接続

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 11 00:33:24 2025
Version 21.3.0.0.0

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



Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SQL> show con_name ; 

CON_NAME
------------------------------
CDB$ROOT
SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
------------------------------
PDB$SEED
READ ONLY

GVISORCL
READ WRITE


SQL> alter session set container = GVISORCL ;

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

SQL> show con_name ; 

CON_NAME
------------------------------
GVISORCL
SQL>

sqlplusでoracle21のパスワード有効期限変更

bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on 火 3月 11 00:47:50 2025
Version 21.3.0.0.0

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



Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

プロファイルが変更されました。

SQL> select resource_name,limit from dba_profiles  where resource_name = 'PASSWORD_LIFE_TIME' and profile = 'DEFAULT' ; 

RESOURCE_NAME
--------------------------------------------------------------------------------
LIMIT
--------------------------------------------------------------------------------
PASSWORD_LIFE_TIME
UNLIMITED


SQL>

ユーザロック状態確認

select user name, account_staus, profile
from dba_users
where username = 'xxx' ;

ユーザロック解除

alter user xxx account unlock ;

ユーザ利用プロファイル変更

alter user xxx PROFILE DEFAULT ;

sqlplusの引数実行

sqlplus xxx/yyy @c:\test.sql
echo %errorlevel%

タイトルとURLをコピーしました