Pages

Thursday, July 28, 2011

Queries




============================================
Display time waited for each wait class.
============================================


SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited
FROM   v$event_name a
       JOIN v$system_event b ON a.name = b.event
GROUP BY a.wait_class;


===============================================
Display session wait information by wait class.
===============================================


SELECT *
FROM   v$session_wait_class
WHERE  sid = &enter_sid;



===============================================
Statistics (delete lock unlock)
===============================================


BEGIN
  DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');
  DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
  DBMS_STATS.unlock_table_stats('MY_SCHEMA','LOAD_TABLE');

END;


=============================================
Retrieve SAMPLE Date (%)
============================================

SELECT e.empno, e.ename, d.dname
FROM   emp SAMPLE (10) e
       JOIN dept d ON e.deptno = d.deptno;




===============================================
past 30 minutes waits
===============================================

select
ash.event,
        sum(ash.wait_time +ash.time_waited) ttl_wait_time
      from v$active_session_history ash
where ash.sample_time between sysdate - 30/1440 and sysdate
group by ash.event
order by 2
/


=================================================
What user is waiting the most(last_hour) ?
=================================================


select sesion.sid,
           sesion.username,
           sum(active_session_history.wait_time +
               active_session_history.time_waited) ttl_wait_time
     from v$active_session_history active_session_history,
           v$session sesion
     where active_session_history.sample_time between sysdate - 1/24 and sysdate
       and active_session_history.session_id = sesion.sid
    group by sesion.sid, sesion.username
  order by 3






==============================================
What SQL is currently using the most resources? (lasthour)
==============================================

select active_session_history.user_id,
           dba_users.username,
           sqlarea.sql_text,
           sum(active_session_history.wait_time +
               active_session_history.time_waited) ttl_wait_time
      from v$active_session_history active_session_history,
           v$sqlarea sqlarea,
           dba_users
     where active_session_history.sample_time between sysdate - 1/24 and sysdate
      and active_session_history.sql_id = sqlarea.sql_id
      and active_session_history.user_id = dba_users.user_id
   group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
  order by 4
/



==============================================
What object is currently causing the highest resource waits? (lasthour)
==============================================


 select dba_objects.object_name,
           dba_objects.object_type,
           active_session_history.event,
           sum(active_session_history.wait_time +
               active_session_history.time_waited) ttl_wait_time
      from v$active_session_history active_session_history,
           dba_objects
     where active_session_history.sample_time between sysdate - 1/24 and sysdate
       and active_session_history.current_obj# = dba_objects.object_id
   group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
  order by 4



===========================================
script to gather database statistics
==========================================

begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY',
gather_sys=>FALSE);
end;



=========================================
script to gather dictionary statistics
========================================


begin
DBMS_STATS.GATHER_DICTIONARY_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER STALE'
);

DBMS_STATS.GATHER_DICTIONARY_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>'for all columns size auto',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>'GATHER EMPTY'
);
end;




========================================
schedule a job (statistics)
=======================================


begin
sys.dbms_scheduler.create_job(job_name => '"SYS"."ESTIMATE100_GATHERAUTO"',
job_type => 'PLSQL_BLOCK',
job_action => 
'begin
DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>''for all columns size auto'',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>''GATHER STALE'',
gather_sys=>FALSE);

DBMS_STATS.GATHER_DATABASE_STATS (
estimate_percent =>100,
block_sample=>FALSE,
method_opt=>''for all columns size auto'',
degree=>null,
cascade=>true,
no_invalidate=>false,
options=>''GATHER EMPTY'',
gather_sys=>FALSE);
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
start_date => trunc(sysdate+1) + 2/24,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Gather auto stats on every table with 100% sampling',
auto_drop => FALSE,
enabled => FALSE);

sys.dbms_scheduler.set_attribute(name => '"SYS"."ESTIMATE100_GATHERAUTO"',
attribute => 'job_priority',
value => 4);

sys.dbms_scheduler.enable('"SYS"."ESTIMATE100_GATHERAUTO"');
end;

Linux Useful Links

Saturday, July 16, 2011

Oracle Datapump parameter REMAP_SCHEMA


Loads all objects from the source schema into a target schema.

Syntax

REMAP_SCHEMA=source_schema:target_schema

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:
> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp


> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_SCHEMA=hr:scott
In this example, if user scott already exists before the import, then the Import REMAP_SCHEMAcommand will add objects from the hr schema into the existing scott schema. You can connect to thescott schema after the import by using the existing password (without resetting it).
If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.

Thursday, July 14, 2011

Basic Introduction to SQL*PLUS


The SQL*PLUS (pronounced "sequel plus") program allows you to store and retrieve data in the relational database management system ORACLE. Databases consists of tables which can be manipulated by structured query language (SQL) commands. 
A table is made up of columns (vertical) and rows (horizontal).
A row is made up of fields which contain a data value at the intersection of a row and a column.
Be aware that SQL*PLUS is a program and not a standard query language.

Getting Started
It is a prerequisite that users are registered for ORACLE, an ORACLE account is needed. 
On Unix platforms you must start the script
 oraenv to set the ORACLE environment. Enter the command . oraenv and press <Return;>. 
Don't forget to type a blanc between the dot and oraenv.
If you are working with a PC using MS Windows, simply use Netinstall to install the product. You can find the software in the database folder. 
Enter
 sqlplus on unix systems or run it on Windows from the start menue. Answer the displayed prompts by entering your ORACLE user-name and password. 
The SQL*PLUS command prompt
 SQL > indicates that you are ready to work.


Some elementary Commands
alter user user identified by newpassword
enables user to change the password
help
accesses the SQL*PLUS help system
exit, quit
terminates SQL*PLUS
ho[st]
leads to the operating system without leaving SQL*PLUS
ho[st] command
executes a host operating system command
ho[st] oerr
accesses the ORACLE error help for unix
Editing and Executing
All entered input is stored as a single SQL*PLUS statement in the command buffer. Pressing the <Return> key while editing will either open a new numbered line or, if the previous line ends with a semicolon or consists of a single slash, will execute the SQL*PLUS command. Opening new numbered lines allows you to structure statements and enables you to refer to particular lines by later using edit functions.
l[ist]
lists command buffer (the current line is marked with a star)
ln or n
makes line n the current line and lists it
ln m
lists lines n through m
a text
appends text to current line
c/oldstring/newstring
changes oldstring to newstring in current line
i
inserts a line after current line
del
deletes the current line
r[un]
runs and lists command buffer
/
runs command buffer
;
lists command buffer

If you use substitution variables, like &variable, instead of values or names in your SQL statement, SQL*PLUS will prompt you and substitute the entered value. A substitution variable is a user variable name preceded by an ampersand.
Working with Command Files
You can use command files to save complex commands. After creating a command file you can retrieve, edit, and run it. The default file extension is .sql . If you use other file extensions you must write the full file name like name.extension.
ed[it]
overwrites a scratch file with the contents of the command buffer
edit enables you to edit this file with the defined host operating system
editor. The name of the scratch file is afiedt.buf .
After leaving the editor the buffer is listed and you can execute it.
ed[it] filename
enables you to edit an existing or new file filename.sql
sav[e] filename
creates file filename and stores the command buffer into it
sav[e] filename [option]
stores command buffer into file filename
Possible options are cre[ate], app[end], rep[lace].
get filename
loads the host operating system file filename into the command buffer
sta[rt] filename [arg1 arg2 ..]
executes file filename
arg1 arg2 .. are arguments you wish to pass to the command file

If you run a command file in which a substitution variable like &1 is used, you will be prompted for that value. You can avoid being prompted by passing an argument to the command file.
Queries and Subqueries
Retrieving data from the database is the most common SQL operation. A query is an SQL command (specifically a select) that retrieves information from one or more tables. A subquery is a select which is nested in another SQL command.


The Describe Command
desc[ribe] name
lists the column definition for table or view name


Basic Select Commands
The basic select command consists of two parts, called clauses: select some data from table.
Examples
select * from tabname
selects all columns and rows from table tabname
select distinct col from tabname
selects column col from table tabname
and returns only one copy of duplicate rows
select col1, col2 ... from tabname
selects specified columns from table tabname
select col1, col2*3 from tabname
selects col1,col2 from table tabname
and lists col1, col2 multiplied by 3
select 2*3 from dual
calculates 2*3 and will display the result
Selecting Rows and Ordering
To retrieve specific rows from a table you need to add a where clause. A where clause consists of one or more search conditions which can be connected by logical operators. To display the retrieved data in a specific order you need to add an order by clause.
Examples
select
 col1,col2 from tabname
where
 col1 < col2 and col2 !=0
order by
 col2
Columns
 col1, col2 are selected from table tabname and all rows where col2 is not equal to zero and col1 is less than col2 are displayed in an ascending order (ordered bycol2). 
select
 col1,col2 from tabname
where
 col1 like '_A%' or col1 like '+++' 
order by
 col2 desc
Columns
 col1,col2 are selected from table tabname and all rows where col1 is equal to '+++' or where the second letter in col1 is an 'A' are displayed in a descending order.
In this example two different escape characters are used. The underscore matches exactly one character whereas the percent sign can match zero or more characters.
 
select
 col1,col2 from tabname
where
 col1 in ( value1,value2 )
Columns
 col1,col2 are selected from table tabname and all rows where col1 is equal to value1 or to value2 are displayed.
select
 col1,col2 from tabname
where
 col1 not between value1 and value2 
Columns
 col1,col2 are selected from table tabname and all rows where col1 is not in the range between value1 and value2 are displayed.


Using Set Operator
Set operators combine the results of two queries into a single result. If a statement contains multiple set operators, they will be evaluated from left to right.
set operator
union
returns all distinct rows selected by either query
union all
returns all rows selected by either query, including all duplicates
intersect
returns all distinct rows selected by both queries
minus
returns all distinct rows selected by the first query but not the second

Example
select * from
 table1
union all
select * from
 table2
This will combine all rows, columns of
 table1 and table2.
Querying Multiple Tables
If you want to retrieve information from different tables, you can do this by issuing different queries or a single JOIN query. In a JOIN query, you list the names of the tables you are querying in the from clause and the names of the linking columns in the where clause. The omission of the linking where clause causes a cartesian product of both tables. A JOIN combines rows from two or more tables where columns which the tables have in common match. If a column name is not unique, you must use a prefix to make clear which column from which table you want to select (e.g. tablename.columnname).
Simple Join
select col1,tab1.col2,col3 from tab1,tab2
where
 tab1.col2=tab2.col2
This is the most common type of join. It returns rows from two tables based on an equality condition, therefore it is also called an
 equi-join.


Non-Equi Join
select tab1.col1,tab2.col2 from tab1,tab2
where
 tab1.col1 between lowval and highval
Since this join doesn't return rows based on a equality condition, it is called a non-equi join.


Self Join
select alias1.col1,alias2.col1 "Header 2" from tabname alias1,tabname alias2
where
 alias1.col2=alias2.col3
In this example the table
 tabname is joined with itself. Using of two different alias names for the same table allows you to refer to it twice. Since the names of the resulting columns in this example are the same, the second column gets a new header. 


Outer Join
select col1,col2 from tab1,tab2
where
 tab1.col1=tab2.col2(+)
Suppose you want to retrieve information from two tables where not all rows match but the result should contain all values from one or more columns. A simple join will select only matching rows whereas the outer join extends the result. All matching rows will be selected and when you append the outer join operator (+) to a column name, those rows which do not match will also be selected. In the example the number of rows which are selected is the number of rows in table
 tab2. If rows match, the outer join works as a simple join, if not, the values from tab2.col2 and a NULL value for the non existing value of tab1.col1 will be selected.


Data Definition Language DDL
DDL commands allow you to create, alter and delete objects (e.g tables, views) and also to grant and revoke privileges. 
create table
 tabname (col1 type1,col2 type2,...) creates table tabname
col1 ... coln are the column names, type1,type2.. specifies the datatype of a column which can be number, date, char, varchar.
number(p,s) specifies a fixed point number having precision (total number of digits) and scale s (number of digits to the right of the decimal point).
number(p) specifies a fixed point number.
number specifies a floating point number.
char(size) specifies fixed length (max 255) character data of length size.
varchar2(size) specifies variable length (max 2000) character string having a maximum length of size bytes.

create table
 tabname as subquery creates table tabname
subquery inserts rows into the table upon its creation. A subquery is a form of the select command which enables you to select columns from an existing table.

create view
 viewname as subquery creates view viewname
A view is a logical table based on one or more tables.
drop table tabname
removes table tabname from the database
alter table tabname add (col1 type1,col2 type2,...)
adds columns to table tabname
alter table tabname modify (col1 type1,col2 type2,...)
modifies column definitions
rename oldname to newname
renames table oldname
alter user user identified by newpassword;
enables user to change the password
to newpassword
grant privilege on object to user
grants a privilege to user
revoke privilege on object from user
revokes a privilege from user


Data Manipulation Language DML
DML commands manipulate and query data in existing tables. These commands do not commit current actions.
insert into tabname (col1,col2...) values (val1,val 2...)
inserts rows into table tabname
insert into tabname subquery
inserts rows(selected by a subquery) into
&table tabname
update tabname set col1=expr1,col2=expr2... where cond
updates rows in table tabname
columns are set to values of expressions if
condition cond is true
update tabname set (col1,col2...)=(subquery) where cond
updates rows in table tabname
columns are set to selected values if
condition cond is true
delete from tabname [where cond]
either deletes all rows from table tabname
or rows where cond is true


Schema
When you select data from a table or you insert data into a table then this object has to be in your own schema. In other words, you must be the owner. 
If you are not the owner of the object, but the owner granted some privileges to you, you have to specify schema.tabname.
Example
select * from scott.emp


Transaction Control Commands
Transaction Control Commands manage changes made by Data Manipulation Language commands. A transaction (or logical unit of work) is a sequence of SQL statements that ORACLE treats as a single unit. A transaction ends with a commit, rollback , exit, or any DDL statement which issues an implicit commit. In most cases transactions are implicitly controlled.
commit
makes all changes since the beginning of a transaction permanent
rollback
rolls back (undoes) all changes since the beginning of a transaction
rollback to savepoint savep
rolls back to savepoint savep
savepoint savep
defines savepoint savep