Infra DevOps

インフラ構築のナレッジや、運用の自動簡易化に関する記事を書いていこうと思います

Oracleの操作メモ

自分用にOracle操作のメモ

 

 

 

Oracle環境変数設定

export ORACLE_SID=SID名
export ORACLE_BASE=/u01/app/oracle
export PATH=$PATH:/u01/app/oracle/product/12.1.0.2/dbhome_1/binexport ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export NLS_LANG=American_Japan.UTF8

 

■表領域作成
CREATE TABLESPACE DATA01 DATAFILE

'+DATA/SID/datafile/

DATA01.dbf' SIZE 100M ;

'+DATA/SID/datafile/IDX03.dbf' SIZE 100M ;

 

■表領域削除
DROP TABLESPACE 表領域名 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;


■テーブル作成
CREATE TABLE ZIP(
    publicCode      NUMBER(5)      NOT NULL,
    OldZipcode      VARCHAR2(5)    NOT NULL,
    Zipcode         VARCHAR2(7)    NOT NULL,
    PrefectureKana  VARCHAR2(30)   NOT NULL,
    CityKana        VARCHAR2(300)  NOT NULL,
    TownKana        VARCHAR2(300)  NOT NULL,
    Prefecture      VARCHAR2(30)   NOT NULL,
    City            VARCHAR2(300)  NOT NULL,
    Town            VARCHAR2(300)  NOT NULL,
    TownDivideFlg   NUMBER(1)      NOT NULL,
    KoazaBranchFlg  NUMBER(1)      NOT NULL,
    ChomeFlg        NUMBER(1)      NOT NULL,
    HasSomeTownsFlg NUMBER(1)      NOT NULL,
    UpdateStatus    NUMBER(1)      NOT NULL,
    UpdateReason    NUMBER(1)      NOT NULL
  ) TABLESPACE HASHIDATA ;

 

■テーブル削除

DROP TABLE AIUEO CASCADE CONSTRAINTS PURGE ;


■テーブル内のレコード数
conn ユーザ名
set line 200
set pages 1000
col TABLE_NAME format a60

select
   table_name,
   to_number(
     extractvalue(
       xmltype(
    dbms_xmlgen.getxml('select count(*) c from '||table_name))
       ,'/ROWSET/ROW/C')) count
 from user_tables
 WHERE TABLE_NAME NOT LIKE 'BIN$%'
   and (iot_type != 'IOT_OVERFLOW' or iot_type is null)
order by table_name;


■表領域確認
SQL> set line 200
SQL> set pages 1000
SQL> col FILE_NAME format a60
SQL> SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;


■ASM接続方法
export ORACLE_HOME=/u01/app/12.1.0.2/grid
export ORACLE_SID=+ASM1
sqlplus / as sysdba


■ASM領域確認手順
select group_number,name,state,type,total_mb as "TOTAL(MB)",
free_mb as "FREE(MB)" ,round(free_mb/total_mb,4)*100 as "FREE(%)"
from v$asm_diskgroup;


SQL> select group_number,name,state,type,total_mb as "TOTAL(MB)",
  2  free_mb as "FREE(MB)" ,round(free_mb/total_mb,4)*100 as "FREE(%)"
  3  from v$asm_diskgroup;


■接続セッション情報確認
select
 sid,serial#,username,osuser,program,machine,terminal
 from v$session
 where type = 'USER';

 

■セッション数の制限値と現行セッション最大数調査

SELECT RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('processes','sessions');

 

■オープンカーソル数調査

SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE  a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;


OCR情報確認
$ srvctl config database -d DB名 -a

 

クラスタデータベース停止
srvctl stop database -d orcl


■ASMディスクからローカルへのコピー
ASMCMD> cp +DATA/SID/PARAMETERFILE/spfile.271.919071759 /var/tmp/spfile.271.919071759


■rmanジョブ確認
SQL> COLUMN CLIENT_INFO FORMAT a30
SQL> COLUMN SID FORMAT 999
SQL> COLUMN SPID FORMAT 9999
SQL> SELECT s.SID, p.SPID, s.CLIENT_INFO
  2  FROM   V$PROCESS p, V$SESSION s
  3  WHERE  p.ADDR = s.PADDR
  4  AND    CLIENT_INFO LIKE 'rman%';

 

■rman進捗状況確認

select SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
2 FROM V$SESSION_LONGOPS
3 WHERE OPNAME LIKE 'RMAN%'
4 AND TOTALWORK !=0
5 AND SOFAR <> TOTALWORK
6 ;

 

 

スキーマ内テーブルサイズ一覧

SELECT
table_name,
TO_NUMBER(
extractvalue(
xmltype(
dbms_xmlgen.getxml('SELECT COUNT(*) c FROM '||table_name)),'/ROWSET/ROW/C')) rec_ccount, trunc(bytes/1024,0) tablesize
FROM user_tables
join user_segments on user_segments.segment_name = user_tables.table_name


スキーマ一覧取得
SQL> select username from dba_users;


スキーマ作成
CREATE USER TEST_USER DEFAULT TABLESPACE TEST_TABLESPACE TEMPORARY TABLESPACE temp IDENTIFIED BY "TEST_USER" PROFILE TEST_PROFILE;

GRANT CONNECT, RESOURCE TO TEST_USER ;

 

 

■ユーザ一覧の確認

SELECT DISTINCT USERNAME FROM ALL_USERS ORDER BY USERNAME ;

 

■ユーザのプロファイル確認

SELECT USERNAME,PROFILE FROM DBA_USERS;

 

■ユーザのロール確認

set line 200
set pages 200
col GRANTEE format a30
col GRANTED_ROLE  format a30

SELECT * FROM DBA_ROLE_PRIVS ORDER BY GRANTEE ;

 

■ユーザのオブジェクト権限確認

 col grantee format a30
col owner format a10
col table_name format a30
col privilege format a10

select grantee, owner, table_name, privilege
from dba_tab_privs
where grantee not like 'PUBLIC' ★任意
order by grantee ;

 

■ユーザのクォータ確認

select * from dba_ts_quotas where USERNAME = 'USER' ;

 

■ユーザデフォルト表領域確認

SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS ;