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