SQL & PL/SQL
Submitted by lodopidolo on Wed, 2010-05-26 13:50
Sometime is necessary to move all database objects from one tablespace to another.
Basically are tables, indexes and lobs.
This script permit move database objects from one user and/or tablespace to another:
Submitted by cihandemir on Sat, 2010-05-08 18:49
Original blog post in here.
Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.
Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character
Submitted by jp_vijaykumar on Wed, 2010-04-14 22:56
KILLING USERS SESSIONS IN ORACLE
Author JP Vijaykumar Oracle DBA
Date Apr 14th 2010
A detailed discussion on redo, undo and Oracle's read consistency are
beyond the scope of this document.
A detailed discussion on setup, troubleshooting of user connections
in MTS environment is beyond the scope of this document.
Before embarking on a killing spree of Oracle sessions, let us explore
the options, limitations, dos and don'ts.
Submitted by wakula on Sat, 2010-01-30 19:59
I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".
Submitted by International_DBA on Wed, 2010-01-27 07:28
SQL> rem Ejemplo con TRUNCATE:
SQL> rem Para empezar hay que crear una tabla:
SQL> create table truncate_example as select * from dba_tables
SQL> rem La tabla tiene muchas líneas:
SQL> select count(*) from truncate_example
Submitted by wakula on Sat, 2010-01-23 08:07
This is my first entry here.
I would like to criticize criticism for case sensitive object naming in Oracle and PL/SQL.
Submitted by jp_vijaykumar on Mon, 2009-12-14 18:36
KILLING SESSIONS IN ORACLE
Author JP Vijaykumar
Date Jan 27th 2009
Modified Jan 29th 2009
One of my procedure is taking long time to complete. I want to kill my job and
re-run the procedure after incorporating HINTS.
Let us explore the options to kill long running jobs.
From the v$session, I found that my session is active.
select username, osuser,sid,serial#,status from v$session where sid=57;
Submitted by atchaya on Thu, 2009-12-10 06:14
I have worked in a table with one timestamp field namely Actiondate. We have updated the sysdate value of inserting time to that field. It contains around 30 lakhs records.
I want to get the records which are inserted a particular day.
I run the following 2 queries..
select * from history
where trunc(actiondate) > trunc(sysdate)
order by docintno
select * from history
where trunc(actiondate) > '01-dec-2009'
order by docintno
Both are taking too much time to give the results.
Submitted by simitechy on Thu, 2009-12-10 02:16
Both SQL Trace and TKPROF help to find the statistics of an SQL statement which could be used for query optimization.
Start Oracle SQLTRACE:
In Oracle, to start an SQLTRACE for the current session, execute:
ALTER SESSION SET SQL_TRACE = TRUE;
Stop Oracle SQLTRACE
In Oracle, to stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;
Submitted by sarathannapareddy on Tue, 2009-12-01 14:22
SQL for SAS Programmers - Introduction
What is SQL?
SQL stands for Structured Query Language and was designed for development and maintenance within a Database Management System (DBMS). A DBMS consists of one or more tables of data, typically joined in a hierarchical fashion, and a series of programs for organizing the data.
Typical tasks performed with SQL code include the following: