ORACLE MANAGEMENT

desc table from dictionary:
SELECT * FROM dictionary;
SELECT * FROM dict_columns WHERE table name='[table_name]’;
desc package:
desc [package_name];
check init parameters valid values:
SELECT * FROM v$parameter_valid_values;
prepare script to rebuild indexes:
SELECT 'ALTER INDEX [user].’ || segment_name || ’ REBUILD TABLESPACE [ts];’ FROM dba_segments WHERE segment_type=’INDEX’ and owner='[user]’;
check tables with tablespaces:
SELECT segment_name,tablespace_name,owner FROM dba_segments WHERE segment_type=’TABLE’ and owner='[user]’ ORDER BY tablespace_name;
prepare script for roleback pending transactions:
SELECT 'EXEC DBMS_TRANSACTION.ROLLBACK_FORCE(”’ || local_tran_id || ”’);’ FROM dba_2pc_pending WHERE upper(state)=’PREPARED’;
check chars:
SELECT ””” FROM dual;
add message to alertlog:
EXEC sys.dbms_system.ksdwrt(2, 'My own message’);
set date:
ALTER SESSION SET nls_date_format =’yyyy-mm-dd hh24:mi:ss’;
check client NLS:
@[%NLS_LANG%] – na w$
@[$NLS_LANG] – na nixach
roll sequence:
SELECT [sekwencja].nextval FROM dual;
check current session id :
SELECT sid FROM v$session WHERE audsid = sys_context(’userenv’,’sessionid’);
check user with process:
SELECT p.addr,s.machine,s.username,p.spid FROM v$process p,v$session s WHERE p.addr=s.paddr AND s.type != 'BACKGROUND’ ORDER BY 3;
check current SQL:
SELECT u.sid, substr(u.username,1,12) user_name, s.sql_text FROM v$sql s,v$session u WHERE s.hash_value = u.sql_hash_value AND sql_text not like '%from v$sql s, v$session u%’ORDER BY u.sid;
check data from past (for rows which were DELETE,INSERT,UPDATE):
SELECT * FROM [table] as of timestamp to timestamp(’2008-01-01 10:00:00′,’YYYY-MM-DD HH24:MI:SS’);
SELECT * FROM [table] AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL ’60’ minute;
depend on param UNDO_RETENTION [sec] setting dynamicaly,
SELECT tuned_undoretention as time[s] FROM v$undostat;
check timezone in database and os:
SELECT dbtimezone FROM dual;
SELECT sessiontimezone FROM dual;
SELECT systimestamp FROM dual;
SELECT current_timestamp FROM dual;
check source:
SET LONG 10000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘PRETTY’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘TABLESPACE’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘REF_CONSTRAINTS’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS_AS_ALTER’, false);
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) from dual;

SELECT text FROM dba_source WHERE owner=[user] and name=[name of: procedure,package,package body,type,type body,trigger,function] ORDER BY line;
check source of ts UNDO_TS:
set long 2000
SQL> SELECT dbms_metadata.get_ddl(’TABLESPACE’,’UNDO_TS’) from dual;
check source of user SYSMAN:
SELECT dbms_metadata.get_ddl(’USER’,’SYSMAN’) FROM dual;
check long operation progress:
SELECT sofar,totalwork,time_remaining/60 MIN,message FROM v$session_longops where sid=[sid];
install TOra at LINUX (as user ORACLE):
apt-get install libqt4-sql
apt-get install libqscintilla2-3
ściągnąć pakiet tora_2.0.0-1-10g-hardy_i386.deb
przy instalacji muszą być ustawione zmienne ORACLE_HOME i LD_LIBRARY_PATH
remove data:
– with ts UNDO
DELETE FROM [table_name];
commit;
– without ts UNDO, reset HWM, without WHERE
TRUNCATE TABLE [table_name]
check last 10 session from remote hosts:
SELECT * FROM (SELECT a.session_id,b.machine,b.logon_time FROM v$active_session_history a,v$session b WHERE a.session_id=b.sid and upper(b.machine) not like '%[SERVER_NAME]%’ GROUP BY a.session_id,b.machine,b.logon_time ORDER BY b.logon_time) WHERE rownum <10;