Tag: Oracle Blog .

Cursor in EXECUTE IMMEDIATE

Posted on by Osama Mustafa in Database
Sometimes you want to use cursor without Open Cursor,you can use ref cursor .

syntax :

declare
   SQL_Text varchar2(32760) := 'qurey'; --your query goes here
   cur sys_refcursor;
begin
   open cur for SQL_Text;
end;
 

example :
V_query := ‘Cursor statement’ ;

declare
  rc sys_refcursor;
begin
  open rc for ;
  loop
    fetch rc into variable;
    exit when rc%notfound;
    <do your process.>
  end loop;
  close rc;
end;

Link Useful :
1-Blog
2-Cursor Loop Example 

Enjoy

Osama mustafa

UTL_MAIL Or send email from Oracle Database

Posted on by Osama Mustafa in Database
<!– @page { margin: 0.79in } P { margin-bottom: 0.08in }
–>

Steps to enable Mailing from Database:
1. sqlplus ‘/ as sysdba’
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb
  SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 
4. Set smtp_server information in init.ora or spfile.ora like the following you have to change with right configuration for yourself :

alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;

Note : 25 = Default SMTP Port
If instance had been started with spfile

eg: alter system set smtp_out_server = ’172.25.90.165:25′ scope=both;

Thats It, your database is configured to send emails ….
How to send an email
1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘omustafa@savvytek.com’, recipients => ‘omustafa@savvytek.com’, subject => ‘database alert’, message => ‘database is corrputed’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to omustafa;
Enjoy 
osama mustafa

Table Locks

Posted on by Osama Mustafa in Scripts
query to get the locked tables in oracle :

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.’||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id  ;

Or you can do the below :

 select * From v$locked_object;
select * From v$session where SID = ”;
select * from dba_objects where object_id = ”;

 Locked objects :

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION ‘SID,Serial#’ IMMEDIATE; 

Osama Mustafa

drop all tables in a tablespace

Posted on by Osama Mustafa in Scripts
set echo off
prompt
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt
prompt specify tablespace name 1:
define tsname=&1
prompt Add purge clause, y for yes, n for no 2:
define prg=&2
prompt enter output sql file name 3:
define filename=&3
set heading off
set verify off
set feedback off
start del_ts_tb.sql
set verify on
set heading on
set echo on
set feedback on
spool &filename
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off 




Osama mustafa

knowing the transaction status

Posted on by Osama Mustafa in Scripts
**All tranasactions/sid/username

select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;


**All tranasactions/sid/username/first 64 bytes of SQL:

select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

Enjoy

Osama mustafa

Use TABLE_EXISTS_ACTION in Impdp

Posted on by Osama Mustafa in Database
Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is ‘SKIP‘, so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND – The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE – If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE – This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Shell Script For Cold Backup

Posted on by Osama Mustafa in Scripts
This shell script selects the datafiles, logfiles and control files,
 tars and gzips them and then sends them to a remote host via rsh.

Download Scripts : Cold_backup.sh

I have to upload the script since its contain codes can’t be appeared on Blog . 

<eofsql 0="" 120="" <eofsql eofsql

Enjoy
osama mustafa

Manage Oracle Files / Rename Or Move Logfiles Part-2

Posted on by Osama Mustafa in Database
In First Topic Part 1 we talked about how to move/Rename Control File Today the same topic but for different file LogFile Let start 

SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG

3 rows selected.

SQL>

To move or rename a logfile do the following.

  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database in mount mode.
  • Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
  • Open the database.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
 
Rename/Move logfile to what you want/where you want 
 
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
 
 ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG' -
>  TO 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_REDO01.LOG'; 

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
 
 
 
The Last topic will be About DataFiles . 
Enjoy 
 
Osama mustafa

ORA-1122 :database file %s failed verification check

Posted on by Osama Mustafa in Database
After trying to make Partation on tables i received the following Error :

ORA-1122 :database file %s failed verification check
ORA-1110 : datafile :
ORA-1207: ORA 1207 file is more recent than control file

Solution

1.Mount the database
SQL> Startup mount

2. Save the information from the control file:
SQL> Alter database backup controlfile to trace;

3. Create a control file creation script from the tracefile generated in user_dump_dest.
Use the Noresetlogs option

4. Shutdown the database and start it in NOMOUNT mode

SQL> shutdown abort
SQL> startup nomount

5. Create the control file
6.Recover the database
SQL> recover database;

7. Open the database
SQL> Alter database open;

Thank you
Osama mustafa

Rename/Move Oracle Files / Control FIle Part 1

Posted on by Osama Mustafa in Database
In this Topics i will post how to move control file to another detestation , i will post other ORACLE FILES :

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL>
 
OR
 
SQL> show parameter control_files
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\ORCL
                                                                      \CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\ORCL\CONTROL03.CTL
SQL>

To move or rename a controlfile do the following.

  • Alter the control_files parameter using the ALTER SYSTEM comamnd.
  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database.
SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL' SCOPE=SPFILE;
 
 
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
 
RE-NAME CONTROL FILE TO THE SAME WE DID ON ALTER STATMENT .
 
SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>
 
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL> 

the same steps for move control file .
W

We will continue 
 
Enjoy 

osama mustafa