ORACLE MANAGEMENT

compile invalid objects

exec dbms_utility.compile_schema('[USERNAME]')

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';

restrict access:

ALTER SYSTEM [ENABLE | DISABLE] RESTRICTED SESSION
SELECT logins FROM v$instance;

check client NLS:

sql> @[%NLS_LANG%] - na w$
sql> @[$NLS_LANG] - na nixach

roll sequence:

SELECT [seq_name].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 DDL source:

SET LONG 100000
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=[object_name] and type=[type] ORDER BY line;
SELECT name,text FROM dba_source WHERE owner=[user] and type=[type] and lower(text) like lower('%[text]%');

e.g. check source of tablespace UNDO_TS:

set long 2000
SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','UNDO_TS') from dual;

e.g. 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];

remove data:

- with tablespace UNDO:
DELETE FROM [table_name];
commit;
- without tablespace 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;

run script to compile all objects:

[ORACLE_HOME]/rdbms/admin/utlrp.sql
which launch script
[ORACLE_HOME]/rdbms/admin/utlprp.sql with param=0
- param=0 - parallel run equal CPU_COUNT
- param=1 - single run
- parametr=N - N number of parallel run

run SQL from bash:

script.sh

sqlplus /nolog lsls <<EOF
connect / as sysdba
select username from dba_users;
EOF

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 file
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_[home] - ORA_LPENABLE = 1