Category: Oracle
Posted by: hajime osako
%uFF20IT%uFF1AOracle%u7BA1%u7406%u8005%u306E%u305F%u3081%u306ESQL%u30EA%u30D5%u30A1%u30EC%u30F3%u30B9

GRANT {system_privilege | ALL PRIVILEGES}
[, system_privilege ]
TO {user | role | PUBLIC} [, {user | role} ]
[IDENTIFIED BY password]
[WITH ADMIN OPTION];
Category: Oracle
Posted by: hajime osako
How can I find Table, Index and Column which are indexed? : Oracle FAQ - dbapool.com

select index_name , table_name , column_name from user_ind_columns order by table_name ;
Category: Oracle
Posted by: hajime osako
Category: Oracle
Posted by: hajime osako
OTN Discussion Forums : How to get package information from ...

DESC ABC

select text from user_source where name='ABC' and type='PACKAGE BODY';
Category: Oracle
Posted by: hajime osako

11/16: Oracle > tips

Category: Oracle
Posted by: hajime osako
sqlplus:
set linesize 160
set pagesize 10000
set numformat 99999999999999999999999
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
set serveroutput on

Performance check:
V$SESSION_WAIT
V$SYSTEM_EVENT
Category: Oracle
Posted by: hajime osako
%u30C7%u30FC%u30BF%u30D9%u30FC%u30B9%u3092%u7279%u5B9A%u3059%u308B%u540D%u524D - %u30AA%u30E9%u30AF%u30EB%u30FBOracle%u3092%u30DE%u30B9%u30BF%u30FC%u3059%u308B%u305F%u3081%u306E%u57FA%u672C%u3068%u4ED5%u7D44%u307F

DBID (Database Identifer)
SELECT DBID,NAME,DB_UNIQUE_NAME,CURRENT_SCN,LOG_MODE FROM V$DATABASE;

SID (System Identifer)
Used in the host server to access shared memory.
Set in environment variables

INSTANCE_NAME
Used from outside of the host.
show parameter instance_name

DB_NAME
show parameter db_name

GLOBAL_NAME
Used to create DATABASE LINK

SERVICE_NAME
Used by LISTENER / TNSNAMES
show parameter service_name

10/28: Oracle > Memo

Category: Oracle
Posted by: hajime osako
Data Dictionary:

V$SHARED_SERVER_MONITOR
V$DISPATCHER
V$SHARED_SERVER
v$CIRCUIT



Category: Oracle
Posted by: hajime osako
DBAsupport.com : Oracle 9i Central : Oracle Time Zone

select DBTIMEZONE from dual;
ALTER database SET TIME_ZONE = 'UTC';

select SESSIONTIMEZONE from dual;
alter session set TIME_ZONE='-03:00';
Category: Oracle
Posted by: hajime osako
Get table and index DDL the easy way

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;


# funcy spool file name
COLUMN system_date NEW_VALUE YYYYMMDD
SELECT to_char(sysdate,'YYYYMMDD') system_date FROM DUAL;

spool myspool_&&YYYYMMDD..log


select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;