Oracle備忘録

http://www.youtube.com/watch?v=IzT1WXfhJCQ

  • Oracle XE セットアップ

https://fedorahosted.org/spacewalk/wiki/OracleXeSetup

    • ちゃんと書いてあるバージョンでインストールしよう

service oracle-xe stop

  • sqlplus

sqlplus jboss/jboss@orcl
su - oracle
sqlplus 'sys/oracle@//localhost/XE as sysdba'
sqlplus 'oracle/oracle@//localhost/XE'
sqlplus 'oracle2/oracle2@//localhost/XE'
sqlplus 'tkobayas/tkobayas@orcl'

  • テーブル見る

select table_name, tablespace_name, status from user_tables;
SELECT TNAME FROM TAB;

  • 制約見る

select * from user_constraints where table_name = 'テーブル名';
テーブル名は大文字で！

  • ファイルからSQL実行

sqlplus 'sys/oracle as sysdba' @cleanup_oracle2.sql

  • ユーザ削除＆作成
                                • -

sqlplus 'sys/oracle as sysdba'
drop user oracle2 cascade;
create user oracle2 identified by oracle2 default tablespace users;
grant dba to oracle2;

                                • -
  • インデックス調べる

select i.table_name, i.index_name, i.uniqueness, c.column_name from user_indexes i, user_ind_columns c where i.index_name = c.index_name and i.table_name = 'XXX';
テーブル名は大文字で！

  • シーケンス

select * from user_sequences where sequence_name = "XXX";

  • EXPLAIN PLAN

explain plan for select hogehoge;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

  • 実行されたSQL
select last_active_time,parsing_schema_name,sql_text, dbms_lob.substr(SQL_FULLTEXT,1000,1) as FULL from v$sqlarea
where parsing_schema_name = 'SYSTEM'
order by last_active_time desc;

where SQL_TEXT LIKE '%ABCD%' とか

SQL_FULLTEXT は CLOB

  • ORA-00001: unique constraint (XXX.SYS_C00XXXXX) の SYS_C00XXXXX ってなんだっけ

http://stackoverflow.com/questions/3881574/general-error-1-ocistmtexecute-ora-00001-unique-constraint-hr-sys-c004023-v

select table_name
from all_constraints
where owner = 'HR'
and constraint_name = 'SYS_C004023';

select column_name
from all_cons_columns
where owner = 'HR'
and constraint_name = 'SYS_C004023';

sqlplus

show linesize
set linesize 列数
COL column_name FOR a40

import/export

前準備
$ sqlplus 'sys/oracle as sysdba'
SQL> create or replace directory TEST_DIR as '/u01/app/oracle/work01';
SQL> grant read, write on directory TEST_DIR to system


impdp system/oracle schemas=MY_USER directory=TEST_DIR dumpfile=MY_USER.DMP logfile=impdp.log

ORA-28002: the password will expire within 7 days

alter profile default limit password_life_time unlimited;
alter user identified by ;

LOB のサイズ調べる

SELECT PROCESSINSTANCEINFO.INSTANCEID, DBMS_LOB.GETLENGTH(PROCESSINSTANCEBYTEARRAY) FROM PROCESSINSTANCEINFO;

Lob segment が大きい

https://nguyentichthanh.wordpress.com/how-to-determine-the-actual-size-of-the-lob-segments-and-how-to-free-the-deletedunused-space-abovebelow-the-hwm-doc-id-386341-1/

select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='<table owner>';
SELECT TABLE_NAME, COLUMN_NAME  FROM DBA_LOBS WHERE OWNER = ‘<owner>’ AND  SEGMENT_NAME= ‘<lob segment name>’ ;
select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;