Generate Table and Constraint DDLs – Oracle

Generate Table DDL Oracle

Categories: Uncategorized

Generate data insert script as insert into

Declare
v_table_name varchar2(30) := ‘MY_TABLE’; — Your Tablename
v_column_list varchar2(4000);
v_insert_list varchar2(4000);
v_ref_cur_columns varchar2(4000);
v_ref_cur_query varchar2(4000);
v_ref_cur_output varchar2(4000);
V_Column_Name Varchar2(4000);
Cursor C1 Is Select Column_Name, Data_Type From DBA_Tab_Columns Where Table_Name = V_Table_Name Order By Column_Id;

refcur sys_refcursor;
begin
for i in c1 loop
v_column_list := v_column_list||’,’||i.column_name;
if i.data_type = ‘NUMBER’ then
v_column_name := i.column_name;
elsif i.data_type = ‘DATE’ then
v_column_name :=
chr(39)||’to_date(‘||chr(39)||’||chr(39)’||’||to_char(‘||i.column_name||’,’||chr(39)||’dd/mm/yyyy hh:mi:ss’||chr(39)||’)||chr(39)||’||chr(39)||’, ‘||chr(39)||’||chr(39)||’||chr(39)||’dd/mm/rrrr hh:mi:ss’||chr(39)||’||chr(39)||’||chr(39)||’)’||chr(39);
elsif i.data_type = ‘VARCHAR2’ then
v_column_name := ‘chr(39)||’||i.column_name||’||chr(39)’;
end if;
v_ref_cur_columns := v_ref_cur_columns||’||’||chr(39)||’,’||chr(39)||’||’||v_column_name;
end loop;
v_column_list := ltrim(v_column_list,’,’);
v_ref_cur_columns := substr(v_ref_cur_columns,8);

V_Insert_List := ‘INSERT INTO ‘||V_Table_Name||’ (‘||V_Column_List||’) VALUES ‘;
V_Ref_Cur_Query := ‘SELECT ‘||V_Ref_Cur_Columns||’ FROM ‘||V_Table_Name;

open refcur for v_ref_cur_query;
loop
fetch refcur into v_ref_cur_output;
exit when refcur%notfound;
v_ref_cur_output := ‘(‘||v_ref_cur_output||’);’;
v_ref_cur_output := replace(v_ref_cur_output,’,,’,’,null,’);
v_ref_cur_output := replace(v_ref_cur_output,'(,’,'(null,’);
v_ref_cur_output := replace(v_ref_cur_output,’,,)’,’,null)’);
v_ref_cur_output := replace(v_ref_cur_output,’null,)’,’null,null)’);
v_ref_cur_output := REPLACE(v_ref_cur_output,’,);’,’,null);’);
v_ref_cur_output := v_insert_list||v_ref_cur_output;
dbms_output.put_line (v_ref_cur_output);
end loop;
End;
/

Categories: Uncategorized

Job scheduling in Oracle

exec dbms_scheduler.create_job(‘upd_dosrub_job’, job_type=>’plsql_block’, job_action=>’ dropme_upd_dosrub;’, start_date => ’18-JUL-14 9.00.00PM US/Pacific’,enabled=>true);
exec dbms_scheduler.set_attribute(‘upd_dosrub_job’, ‘max_run_duration’ , interval ’50’ hours);
select * from DBA_SCHEDULER_JOBS where job_name=’upd_dosrub_job’;

Categories: Uncategorized

view dbms profiler

view dbms_profiler data:
select * from plsql_profiler_runs order by run_date desc;
select * from plsql_profiler_data order by total_time desc;

Cleanup dbms_profiler data:
delete from plsql_profiler_data;

delete from PLSQL_PROFILER_UNITS;
delete from plsql_profiler_runs;

Categories: Uncategorized

Partition an Existing table in Oracle

Alter Session Force Parallel Dml Parallel 32;
alter session force parallel query parallel 32;

Categories: Uncategorized

Find bind variables for a Query

select s.sid,
S.USERNAME,
s.sql_hash_value,
s.sql_id,
s.sql_child_number,
spc.name,
spc.value_string,
last_captured
from V$SQL_BIND_CAPTURE SPC, V$SESSION S,V$SQL SQ
where s.sid=510 and s.sql_hash_value = spc.hash_value
and s.sql_address = spc.address
and SQ.SQL_ID=S.SQL_ID
and SPC.WAS_CAPTURED=’YES’
and S.type<>’BACKGROUND’
and s.status=’ACTIVE’;

Categories: Uncategorized

Transaction Logging Mode in Oracle and Sql Server

December 20, 2012 Leave a comment

Oracle has two options for logging: ARCHIVELOG mode and NOARCHIVELOG mode.

Transactions are still logged to the online redo logs, but the difference here is whether or not the redo logs are archived out to disk when full.

SQL Server has three different logging modes: Full, Bulk Logged and Simple. Full and Simple respectively correspond to the two Oracle methods.

Categories: Uncategorized