| | 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
alter database flashback on;
-- Check if flashback is enabled
select flashback_on from v$database;
select * from v$flashback_database_log; | | 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 | | | | |
|
| | Dropping online redo logs
If you want to move redo logs to another filesystem without shutting down the database you can create new groups on the new filesystem and drop the old groups.
1. Create new online redo log groups
SQL> ALTER DATABASE ADD LOGFILE ('/path2redo/redo4a.log') SIZE 100M;
SQL> ALTER DATABASE ADD LOGFILE ('/path2redo/redo5a.log') SIZE 100M;
SQL> ALTER ... | | More...
06/13/08 | | | | |
|
| | How to enable autoextend on tablespaces and datafiles
-- Create tablespace with a datafile that will be auto extended to 10Gb
create tablespace TABLESPACE_NAME datafile '/path/to/datafile.dbf' size 2000M AUTOEXTEND ON MAXSIZE 10000M extent management local uniform size 5M segment space management auto;
If you want to enable autoextend on a smallfile tablespace (if you are trying to alter tablespace to ... | |
More...
06/04/08 | | | | |
|
|
Page 1 | 2 | 3 >> |
