OracleのSQLやコマンドについてご紹介！！
今日もブログを読んでいただきありがとうございます。本日はOracleのSQLやコマンドについて紹介します。
★表領域の作成
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のリポジトリ、構成ファイル削除(11gまで)
emca -deconfig dbcontrol db -repos drop
★EMの構成ファイル削除(11gまで)
emca -deconfig dbcontrol db
★EMの再構成(11gまで)
emca -config dbcontrol db -repos recreate
以上となります。ご参照ありがとうございました。
