ORACLE SQLやコマンドの備忘録

OracleSQLやコマンドの備忘録


★表領域の作成
CREATE TABLESPACE USERS02
DATAFILE 'F:\APP\ADMIN\ORADATA\O11203\USERS02.DBF' SIZE 100M;


★データファイルを追加
ALTER TABLESPACE USERS ADD DATAFILE
'F:\APP\ADMIN\ORADATA\O11203\USERS03.DBF' SIZE 10M;


★データファイルのサイズ変更
ALTER DATABASE DATAFILE
'F:\APP\ADMIN\ORADATA\O11203\USERS03.DBF' RESIZE 20M;


★データファイルを自動拡張有効化
ALTER DATABASE DATAFILE
'F:\APP\ADMIN\ORADATA\O11203\USERS03.DBF' AUTOEXTEND ON;


★表領域を名前変更
ALTER TABLESPACE USERS02 RENAME TO USERS03;


★表領域を読み取り専用に変更
ALTER TABLESPACE USERS02 READ ONLY;


★表領域を読み下記可能に変更
ALTER TABLESPACE USERS02 READ WRITE;


★デフォルト永続表領域を変更
ALTER DATABASE DEFAULT TABLESPACE USERS02;


★表領域にオブジェクトが含まれる場合も物理ファイルごと削除
DROP TABLESPACE USERS03 INCLUDING CONTENTS AND DATAFILES;


★DBへ接続しているユーザの確認(V$session)
select username, program, service_name, module, action, client_info, machine, osuser
from v$session;


★SCOTTユーザの作成、SCOTTユーザへの権限付与、DEPT表の作成、EMP表の作成
------------------------------------------
CREATE USER SCOTT IDENTIFIED BY tiger
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP QUOTA 100M ON USERS ACCOUNT UNLOCK;

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

CREATE TABLE SCOTT.DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;

INSERT INTO SCOTT.DEPT VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO SCOTT.DEPT VALUES(20,'RESEARCH','DALLAS');
INSERT INTO SCOTT.DEPT VALUES(30,'SALES','CHICAGO');
INSERT INTO SCOTT.DEPT VALUES(40,'OPERATIONS','BOSTON');

CREATE TABLE SCOTT.EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO SCOTT.EMP VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO SCOTT.EMP VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO SCOTT.EMP VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO SCOTT.EMP VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO SCOTT.EMP VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO SCOTT.EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
COMMIT;
---------------------------------------


★UPDATE文の実行
update SCOTT.dept set DNAME=1 where LOC='NEW YORK';


★表へ列を追加する
ALTER TABLE SCOTT.dept
ADD (first_name varchar2(10));


★表から列を削除
ALTER TABLE SCOTT.dept
DROP (first_name);


★索引の追加
CREATE INDEX SCOTT.HOGEIDX ON SCOTT.DEPT(DNAME);


★索引の再編成
ALTER INDEX SCOTT.HOGEIDX REBUILD;


★索引の削除
DROP INDEX SCOTT.HOGEIDX;


★表の削除
DROP TABLE SCOTT.DEPT CASCADE CONSTRAINTS;


★ビューの作成
CREATE VIEW SCOTT.D_VIEW AS SELECT
DEPTNO, DNAME, LOC
FROM SCOTT.DEPT;


★ビューのコンパイル
ALTER VIEW SCOTT.D_VIEW COMPILE;


★ビューの削除
DROP VIEW SCOTT.D_VIEW;


★マテリアライズドビューの作成
CREATE MATERIALIZED VIEW SCOTT.DMV
AS SELECT DEPTNO, DNAME, LOC FROM SCOTT.DEPT;


★マテリアライズドビューログの作成
CREATE MATERIALIZED VIEW LOG ON SCOTT.DMV;


★完全リフレッシュ
exec dbms_mview.refresh('DMV','c');


★高速リフレッシュ
exec dbms_mview.refresh('DMV','f');


★マテリアライズドビューのコンパイル
ALTER MATERIALIZED VIEW SCOTT.DMV COMPILE;


★マテリアライズドビューの削除
DROP MATERIALIZED VIEW SCOTT.D_VIEW;


★マテリアライズドビューログの削除
DROP MATERIALIZED VIEW LOG ON SCOTT.DMV;


★プロシージャの作成と実行
-------------------------------------
CREATE TABLE SCOTT.EMP(
CODE CHAR(4) ,NAME VARCHAR2(50),CONSTRAINT PK_emp PRIMARY KEY (CODE));

INSERT INTO SCOTT.EMP VALUES('0001','tiger');
INSERT INTO SCOTT.EMP VALUES('0002','tom');
INSERT INTO SCOTT.EMP VALUES('0003','jon');

CREATE OR REPLACE PROCEDURE SCOTT.SAMPLE(
I_CODE IN  VARCHAR2,
O_NAME OUT VARCHAR2)
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('CODE:' || I_CODE);
    SELECT NAME
    INTO   O_NAME
    FROM   SCOTT.EMP
    WHERE  CODE = I_CODE;
END;
/

VARIABLE V_NAME VARCHAR2(10);
SET SERVEROUTPUT ON
EXECUTE SCOTT.SAMPLE('0002',:V_NAME);
-------------------------------------


★プロシージャのコンパイル
ALTER PROCEDURE SCOTT.SAMPLE COMPILE;


★プロシージャの削除
DROP  PROCEDURE SCOTT.SAMPLE;


★ログ・スイッチを実行
ALTER SYSTEM SWITCH LOGFILE;


★チェックポイントを実行
ALTER SYSTEM CHECKPOINT;


アーカイブログモードで稼動しているか確認
select log_mode from v$database;


アーカイブログモードに変更
shutdown immediate
startup mount
alter database archivelog;
alter database open;


アーカイブログモードを無効

shutdown immediate
startup mount
alter database no archivelog;
alter database open;


★表の統計情報の収集
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME       => 'SCOTT',
TABNAME      => 'DEPT',
ESTIMATE_PERCENT => 10 );
END;
/


★表の統計情報の削除
exec DBMS_STATS.DELETE_TABLE_STATS(ownname=>'SCOTT',tabname=>'DEPT');


スキーマの統計情報収集
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME       => 'SCOTT');
END;
/


スキーマの統計情報削除
exec DBMS_STATS.DELETE_SCHEMA_STATS(ownname=>'SCOTT');


★DBの統計情報収集
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;


★DBの統計情報削除
exec DBMS_STATS.DELETE_DATABASE_STATS;


★ANALYZEで表の統計収集(11gから推奨しない)
ANALYZE TABLE SCOTT.DEPT COMPUTE STATISTICS;


★統計情報の収集日時などの確認
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
SELECT TABLE_NAME NAME, LAST_ANALYZED, SAMPLE_SIZE, NUM_ROWS FROM DBA_TABLES
WHERE TABLE_NAME = 'DEPT';


★プロファイルの確認
select * from dba_profiles;


★デフォルトプロファイルの確認
select * from dba_profiles
where profile='DEFAULT';


★セッション終了
SELECT SID,SERIAL#,STATUS,SERVER
FROM V$SESSION
WHERE USERNAME = 'SCOTT';

ALTER SYSTEM KILL SESSION '133,61';


★バッファキャッシュのクリア
ALTER SYSTEM FLUSH BUFFER_CACHE;


★共有プールのクリア
ALTER SYSTEM FLUSH SHARED_POOL;


★DBのキャラクタセット確認
select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';


★DB_LINKの権限付与と作成
GRANT CREATE DATABASE LINK TO SCOTT;
CREATE DATABASE LINK testlink CONNECT TO SCOTT IDENTIFIED BY tiger USING 'o11203';


★DB_LINKの削除
drop database link testlink;


★簡易接続
sqlplus scott/tiger@10.10.10.10:1521/o11203


★EMのリポジトリ、構成ファイル削除
emca -deconfig dbcontrol db -repos drop


★EMの構成ファイル削除
emca -deconfig dbcontrol db


★EMの再構成
emca -config dbcontrol db -repos recreate