{"id":70,"date":"2008-10-01T22:05:00","date_gmt":"2008-10-01T20:05:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2008\/10\/01\/oracle-management\/"},"modified":"2010-08-24T22:44:53","modified_gmt":"2010-08-24T20:44:53","slug":"oracle-management","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2008\/10\/01\/oracle-management\/","title":{"rendered":"ORACLE MANAGEMENT"},"content":{"rendered":"

<\/span><\/span><\/p>\n

set large page for winshit (Oracle9i 64-bit for Windows and Oracle10g<\/i> 32-bit and 64-bit for Windows):<\/span><\/span>
turn off lock_sga in init file<\/span><\/span><\/span>
HKEY_LOCAL_MACHINE\/SOFTWARE\/ORACLE\/KEY_[home] – ORA_LPENABLE = 1<\/span>
<\/span><\/span><\/span><\/div>\n

desc table from dictionary:<\/span>
SELECT * FROM dictionary;
SELECT * FROM dict_columns WHERE table name='[table_name]’;<\/span>
desc package:<\/span><\/b>
desc [package_name];<\/span>
check init parameters valid values:<\/span><\/b>
SELECT * FROM v$parameter_valid_values;<\/span>
<\/b>prepare script to rebuild indexes:<\/span>
SELECT 'ALTER INDEX [user].’ || segment_name || ’ REBUILD TABLESPACE [ts];’ FROM dba_segments WHERE segment_type=’INDEX’ and owner='[user]’;
check tables with tablespaces:<\/span>
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:<\/span>
SELECT 'EXEC DBMS_TRANSACTION.ROLLBACK_FORCE(”’ || local_tran_id || ”’);’ FROM dba_2pc_pending WHERE upper(state)=’PREPARED’;
check chars:<\/span>
SELECT ””” FROM dual;
<\/span>add message to alertlog:<\/span>
EXEC sys.dbms_system.ksdwrt(2, 'My own message’);
set date:<\/span>
ALTER SESSION SET nls_date_format =’yyyy-mm-dd hh24:mi:ss’;
<\/span>check client NLS:<\/span>
@[%NLS_LANG%] – na w$
@[$NLS_LANG] – na nixach
roll sequence:<\/span>
SELECT [sekwencja].nextval FROM dual;<\/span>
check current session id :<\/span><\/b>
SELECT sid FROM v$session WHERE audsid = sys_context(’userenv’,’sessionid’);
check user with process:<\/span><\/b>
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;
<\/span>check current SQL:<\/span>
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 <\/span>(for rows which were DELETE,INSERT,UPDATE)<\/i>:
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:<\/span>
SELECT dbtimezone FROM dual;
SELECT sessiontimezone FROM dual;
SELECT systimestamp FROM dual;
SELECT current_timestamp FROM dual;
check source:<\/span>
SET LONG 10000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018PRETTY\u2019, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018SEGMENT_ATTRIBUTES\u2019, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018STORAGE\u2019, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018TABLESPACE\u2019, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018CONSTRAINTS\u2019, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018REF_CONSTRAINTS\u2019, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, \u2018CONSTRAINTS_AS_ALTER\u2019, false);
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) from dual;<\/p>\n

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:<\/span>
set long 2000
SQL> SELECT dbms_metadata.get_ddl(’TABLESPACE’,’UNDO_TS’) from dual;
check source of user SYSMAN:<\/span>
SELECT dbms_metadata.get_ddl(’USER’,’SYSMAN’) FROM dual;
check long operation progress:<\/span>
SELECT sofar,totalwork,time_remaining\/60 MIN,message FROM v$session_longops where sid=[sid];
install TOra at LINUX (as user ORACLE):<\/span>
apt-get install libqt4-sql
apt-get install libqscintilla2-3
\u015bci\u0105gn\u0105\u0107 pakiet tora_2.0.0-1-10g-hardy_i386.deb
przy instalacji musz\u0105 by\u0107 ustawione zmienne ORACLE_HOME i LD_LIBRARY_PATH
remove data<\/b>:<\/span>
– 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:<\/span>
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;<\/p>\n

run script to compile all objects:<\/span><\/div>\n
[ORACLE_HOME]\/rdbms\/admin\/utlrp.sql
kt\u00f3ry wywo\u0142uje skrypt [ORACLE_HOME]\/rdbms\/admin\/utlprp.sql z parametrem=0
– parametr=0 – wywo\u0142anie r\u00f3wnole\u0142e z liczb\u0105 r\u00f3wn\u0105 parametrowi CPU_COUNT
– parametr=1 – wywo\u0142anie pojedyncze
– parametr=N – do wpisania liczba r\u00f3wnoleg\u0142ych wywo\u0142a\u0144<\/div>\n
run SQL from bash:<\/span><\/div>\n
skrypt.sh<\/div>\n
sqlplus \/nolog lsls <
connect \/ as sysdba
select username from dba_users;
EOF<\/eof><\/div>\n","protected":false},"excerpt":{"rendered":"

set large page for winshit (Oracle9i 64-bit for Windows and Oracle10g 32-bit and 64-bit for Windows):turn off lock_sga in init fileHKEY_LOCAL_MACHINE\/SOFTWARE\/ORACLE\/KEY_[home] – ORA_LPENABLE = 1 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].’ || […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[80],"tags":[76],"class_list":["post-70","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle-management"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/70","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=70"}],"version-history":[{"count":1,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/70\/revisions"}],"predecessor-version":[{"id":214,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/70\/revisions\/214"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=70"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=70"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=70"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}