Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums
Online Tools:  Generate rename / copy datafiles script

 
 
 
 Found 25 tips, Displaying 1 - 10 
 
Sort by 
 
 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
 
 
 How to move LOB index to another tablespace
SQL> ALTER TABLE SAPSR3.CNVMBTCLU MOVE LOB (CLUSTD) STORE AS (TABLESPACE PSAPTDMS); -- where CLUSTD is the lob column
 More...
04/21/11
 
 
 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
 
 
 Get list of tablespaces
SQL statement to get a list of all tablespaces in Oracle database: SQL> select * from dba_tablespaces;
 More...
10/08/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 >>

Recent tips & tricks
Databases > Oracle > Streams & CDC : Check number of messages in pr...
Databases > Oracle > Performance Tuning : Get bind variables for a speci...
Databases > Oracle > DR : Data Guard Broker commands
Databases > Oracle > ASM : Copy files between ASM diskgro...

More categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com