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 ;