Bounce the DB

svrmgrl
connect internal
alter system checkpoint;
shutdown abort;
exit

svrmgrl
connect internal
startup;
exit

Attributes Tables:
—————————-
user_ind_columns
user_tab_columns

tnsnames.ora located at $ORACLE_HOME/network/admin/

Analyze Table:
————–
select last_analyzed from user_tables where lower(table_name)=’&table_name’ ;
analyze table ‘&table_name’ estimate statistics sample 5 percent;

Giving Grants:
————–
GRANT ALL on TABLE1 to PUBLIC;

Indexes in Reporting DB:
———————-–
select substr(COLUMN_NAME,0,50) COLUMN_NAME,
substr(INDEX_NAME,0,50) INDEX_NAME
from dba_ind_columns where lower(table_name) = ‘&table_name’
and table_owner=’MR’
order by INDEX_NAME;

Indexes in Production DB:
————————-
select substr(COLUMN_NAME,0,50) COLUMN_NAME,
substr(INDEX_NAME,0,50) INDEX_NAME
from dba_ind_columns@prod
where lower(table_name) = ‘&table_name’
order by INDEX_NAME;

Hint for Insert:
—————-
insert /*+ APPEND */ into table1
select * from table2;
commit;
(Commit is a must - otherwise next query on this will fail)

Procedure Code:
—————
set lines 2000
set pages 2000
select text from user_source where lower(name)=’&proc_name’;

Deleting Duplicate Records:
—————————
DELETE
FROM
daily_facts_all4 a
WHERE EXISTS (
SELECT
NULL
FROM daily_facts_all4 b
WHERE
b.grouping = a.grouping and
b.product = a.product and
b.country = a.country and
b.site_id = a.site_id
GROUP BY
b.grouping, b.product,b.country,b.site_id
HAVING
a.rowid < MAX(b.rowid)
)
and country='US'
and end_date='18-Nov-2004'
;
commit;

Applying Unique Constraint:
—————————
alter table table_name add constraint uni_const_table unique(col1,col2,…coln);

Case Statement:
——————
alter table amit_raw_data add listing_range VARCHAR2(10);

update amit_raw_data
set listing_range = (CASE
WHEN listings >= 1 and listings < 2 THEN ‘1’
when listings >= 2 and listings < 4 THEN ‘2-3’
when listings >= 4 and listings < 11 THEN ‘4-10’
when listings >= 11 and listings < 26 THEN ‘11-25’
when listings >= 26 and listings < 101 THEN ‘26-100’
when listings >= 101 and listings < 201 THEN ‘101-200’
when listings >= 201 and listings < 501 THEN ‘201-500’
ELSE ‘>500’ END);

Active Sessions:
——————
set lines 1111;
set pages 1111;
select sw.sid,s.serial#,sa.sql_text,sw.event,s.schemaname “oracle_user”,
s.osuser “os_user”,s.machine
from v$session_Wait sw,v$sqlarea sa,v$session s
where s.status=’ACTIVE’
and s.sql_hash_value=sa.hash_value
and s.sid = sw.sid ;

Killing them:
alter system kill session ‘,’ ;

Check DB Links Available:
—————————-
select * from user_db_links;

RANK Function:
—————–
The RANK function produces an ordered ranking of rows starting with a rank of one. Users specify an optional PARTITION clause and a required ORDER BY clause. The PARTITION keyword is used to define where the rank resets. The specific column which is ranked is determined by the ORDER BY clause. If no partition is specified, ranking is performed over the entire result set. RANK will assign a rank of 1 to the smallest value unless descending order is used. The following example ranks salesmen for each region based on their sales amount.

SELECT sales_person, sales_region, sales_amount,
RANK() OVER (PARTITION BY s_region ORDER BY s_amount DESC)
FROM Sales_table;

Triggers:
———
CREATE [OR REPLACE] TRIGGER
{ BEFORE|AFTER } { INSERT|DELETE|UPDATE } ON
[REFERENCING [NEW AS ] [OLD AS ]]
[FOR EACH ROW [WHEN ()]]

Example:
CREATE OR REPLACE TRIGGER InsertMemberTeamId?
AFTER INSERT ON MEMBERS
FOR EACH ROW
BEGIN
INSERT INTO MEMBER_TEAM
VALUES(:new.MEMBER_ID, :new.TEAM_ID);
END InsertMemberTeamId?;
/

To drop a trigger:
drop trigger

;To disable or enable a trigger:
alter trigger

{disable|enable};Driving Site Hint:
——————
The DRIVING_SITE hint can be used to force query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The format of the hint is
DRIVING_SITE(table)
where table is the name or alias for the table at which site the execution should take place.

Example:
SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@rsite
WHERE emp.deptno = dept.deptno;

If this query is executed without the hint, rows from dept will be sent to the local site and the join will be executed there. With the hint, the rows from emp will be sent to the remote site and the query will be executed there, returning the result to the local site.