| | How to remove pending distributed transactions
sqlplus '/ as sysdba'
select count(*) from DBA_2PC_PENDING ;
COUNT(*)
----------
22
select max(fail_time) from dba_2pc_pending;
MAX(FAIL_
---------
03-MAR-12
select 'rollback force '''||local_tran_id||''' ;' from dba_2pc_pending ;
'ROLLBACKFORCE'''||LOCAL_TRAN_ID||''';'
--------------------------------------------------------------------------------------
rollback force '15.41.373682' ;
rollback force '18.10.103548' ;
rollback force '20.35.447783' ;
...
select 'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''' )' , 'commit;' from dba_2pc_pending
'EXECDBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||LOCAL_TRAN_ID||''')'
----------------------------------------------------------------------------------------------------
'COMMIT;'
--------------------------------
exec dbms_transaction.purge_lost_db_entry('15.41.373682' )
commit;
exec dbms_transaction.purge_lost_db_entry('18.10.103548' )
commit;
exec dbms_transaction.purge_lost_db_entry('20.35.447783' )
commit;
...
Run ... | | 
More...
06/28/12 | | | | |
|
|
| | View Oracle database properties
To check database properties, for example: default tablespace type, various nls parameters, timezone etc run the following query:
SELECT *
FROM DATABASE_PROPERTIES; | |
More...
02/01/11 | | | | |
|
| | How to enable flashback database in Oracle
-- Set flashback database parameters
alter system set db_recovery_file_dest_size=10G;
alter system set db_recovery_file_dest='/dir/fra';
-- value in minutes
alter system set db_flashback_retention_target=2160;
-- Turn flashback on for the database
startup mount;
alter database flashback on;
alter database open;
-- Check if flashback is enabled
select flashback_on from v$database;
select * from v$flashback_database_log;
--
-- To check the estimated flashback size
--
SELECT ROUND(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024) FROM V$FLASHBACK_DATABASE_LOG;
--
--
-- To ... | |
More...
12/04/09 | | | | |
|
| | Script to find invalid synonyms
select *
from dba_synonyms s
where table_owner not in ('SYS', 'SYSTEM')
and db_link is NULL
and table_name not in
(select object_name
from dba_objects o
where o.object_name = s.table_name
and o.owner = s.table_owner
... | | 
More...
07/09/09 | | | | |
|
| | Set NLS parameter for session in logon trigger
If you are trying to run something like ALTER SESSION SET in logon trigger you will get ORA-04092 or if you are trying to do it with DBMS_SESSION you will get ORA-06512.
The solution is to create a procedure and then call procedure from the trigger i.e.:
--
-- procedure to set NLS_DATE_FORMAT ... | |
More...
03/31/09 | | | | |
|
| | How to Set the DB on the archive log mode ?
1- log on the db (database) as sys dba
>sqlplus >username/password as sysdba
2- shutdown the DB immediate
>sqlplus shutdown immediate
3-start db in mount state
>sqlplus startup mount
4-alter database archive log
5-alter database open
| | 
More...
01/13/09 | | | | |
|
| | Getting ORA-04021 when running grant execute on package?
If you are trying to grant execute on a package and getting ORA-04021 'timeout occurred while waiting to lock object' try the following:
Check who else is using the packages:
SQL> select * from v$access where object = 'PACKAGE_NAME';
If you see another session accessing the package ask the user to logout or ... | | More...
10/31/08 | | | | |
|
|
| | How to purge Oracle recycle bin
To purge recyclebin for a specific user, login as that user and run:
SQL> PURGE dba_recyclebin;
To purge everything:
SQL> PURGE dba_recyclebin;
| |
More...
10/06/08 | | | | |
|
Page 1 | 2 | 3 >> |
