Credit: Most of this document was written by a co-worker, Evelyn Dibben. Thanks Evelyn!
Re-compiling a view
alter view <view name> compile;
Work with procedures in TOAD
don't use the main SQL window. Use the procedure editor: Database->Procedure Editor
click the green forward arrow to compile
you can click on the "DBMS Output" tab in the SQL Editor to view DBMS output, which can be used for
debugging. Click the little red dot and it should turn green and show that DBMS output has
been turned on. For an example of this, run list_ijump_access with the exact same parameters as
has_ijump_access, preferably for a machine you don't have access to. The DBMS output shows why
you don't have access to that machine.
Undo Segment
SELECT /*+ RULE */
T.START_TIME,
rn.name "Rollback segment name",
sess.schemaname "User",
o.name "Table Name",
LCK1.TYPE,
sESS.username "ORACLE USERNAME",
sESS.program,
sESS.MACHINE
FROM
v$rollname rn,
v$session sess,
sys.obj$ o,
v$lock lck1,
v$transaction t
WHERE
sess.taddr = t.addr
AND lck1.sid = sess.sid
AND lck1.id1 = o.obj# (+)
--AND lck1.TYPE = 'TM'
AND t.xidusn = rn.usn
Misc
Query to look at procedure:
select text from user_source where name = 'OASIS_GRANTS' order by line;
Looking at Oracle Meta-Data:
Dba_tables - contains all the tables
Dba_tab_columns - contains all the columns
create table client_system as select * from oasisadmin.client_system;
suck the trigger out of TOAD, create the sequence before compiling the trigger
create sequence <name> start with xxxx;
make a column name not null:
alter table circuit modify client_id number not null
look at materialized views and their statuses:
SELECT * FROM user_mviews WHERE container_name = 'PROJECT_BUDGET_MV'
SELECT RNAME,
ROWNER,
BROKEN,
TO_CHAR(NEXT_DATE, 'DD-MON-YYYY HH:MI:SS AM') next_refresh,
INTERVAL
FROM ALL_REFRESH
ORDER BY 1;
<verbatim>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96568/rarmonit.htm#32254
!!Materialized views:
If you see it and it looks like a regular table, but in Synonyms,
try looking here:
<verbatim>
SELECT * FROM ALL_MVIEWS
WHERE MVIEW_NAME LIKE 'PROJECT_TIME%'
SELECT * FROM dba_jobs
To run a job on-demand:
(the number below is the JOB field in the DBA_JOBS table)
BEGIN
dbms_job.RUN(22);
END;
See all the DB links
select * from dba_db_links
Null values
Oracle doesn’t distinguish between the empty string and null; it is always NULL. This may be another example of how Oracle is different from MySQL.
Oracle does equate empty strings to NULL values. You should always use the IS NULL or IS NOT NULL syntax for checking for missing values.
Consider the following:
CREATE TABLE TEST_NULL
(C1 VARCHAR2(10), C2 VARCHAR2(10))
INSERT INTO TEST_NULL
(C1, C2)
VALUES
(' ','SPACE') -- This is a string consisting of a single space
INSERT INTO TEST_NULL
(C1, C2)
VALUES
('','EMPTY') - This is an empty string
INSERT INTO TEST_NULL
(C1, C2)
VALUES
(NULL,'NULL') - Using the keyword NULL
SELECT * FROM TEST_NULL
WHERE C1 = ' ‘ -- This is a string consisting of a single space - will return the ‘SPACE’ row
SELECT * FROM TEST_NULL -- This is an empty string - will return NO rows
WHERE C1 = ''
SELECT * FROM TEST_NULL
WHERE C1 IS NULL -- Using the NULL keyword will return the ‘EMPTY’ and ‘NULL’ rows !
If you are worried about data where a single blank would have been entered where a NULL is --expected I would recommend using the NVL function:
SELECT * FROM TEST_NULL
WHERE NVL(c1,’ ‘) = ‘ ‘ -- These are single space strings - this will return all rows
By the way
SELECT name
FROM employee_class
WHERE code is not null and code <> 'blah'
ORDER BY name
is equivalent to
SELECT name
FROM employee_class
WHERE code <> 'blah'
ORDER BY name
Oracle treats a NULL value as undefined so if code is null the comparision to ‘blah’ is undefined and will not return any rows.
Date Values
DROP TABLE TEST_DATES
CREATE TABLE TEST_DATES
(D1 DATE,
D2 DATE,
D3 DATE);
- SYSDATE is used for the current date/time
- TRUNC will truncate the time portion - will enter 12:00:00 as the time
- TO_DATE is used to convert a character string into a date
INSERT INTO TEST_DATES
(D1, D2, D3)
VALUES
(SYSDATE, TRUNC(SYSDATE), TO_DATE('17-MAR-2001','DD-MON-RRRR'))
COMMIT;
- Use TO_CHAR to format the date when selecting - not needed but will ensure that the format is consistent
SELECT D1,TO_CHAR(D1,'DD-MON-RRRR HH:MI:SS'),
TO_CHAR(D2,'DD-MON-RRRR HH:MI:SS'),
TO_CHAR(D3,'DD-MON-RRRR HH:MI:SS')
FROM TEST_DATES
- Date comparison
- Dates are stored down to the second D1 != D2
SELECT * FROM TEST_DATES
WHERE D1 = D2
- Use TRUNC if you want to compare just the day
SELECT * FROM TEST_DATES
WHERE TRUNC(D1) = TRUNC(D2)
- Date Math - addition and subtraction is done in days
- Add one day, subtract 30 days, add 30 seconds
INSERT INTO TEST_DATES
(D1, D2, D3)
VALUES
(SYSDATE+1, SYSDATE-30, SYSDATE+(30/86400))
SELECT TO_CHAR(D1,'DD-MON-RRRR HH:MI:SS'),
TO_CHAR(D2,'DD-MON-RRRR HH:MI:SS'),
TO_CHAR(D3,'DD-MON-RRRR HH:MI:SS')
FROM TEST_DATES
- Subtracting two dates will give you days between
SELECT D1,D2,D3,D1 - D2, D2-D3
FROM TEST_DATES
SELECT (D1-D2) * 86400
FROM TEST_DATES
- Useful for metrics reporting:
SELECT MIN(implementation_date - submit_date), MAX(implementation_date - submit_date), AVG(implementation_date - submit_date)
FROM ETICKET
WHERE implementation_date - submit_date > 0
SELECT TO_CHAR(SUBMIT_DATE,'RRRRMM'), MIN(implementation_date - submit_date), MAX(implementation_date - submit_date), AVG(implementation_date - submit_date)
FROM ETICKET
WHERE implementation_date - submit_date >= 0
GROUP BY TO_CHAR(SUBMIT_DATE,'RRRRMM')
SELECT d.employee_id, d.fixed_name, a.deca_id, a.status
FROM EMPLOYEE d, DECOM_ACTION a
WHERE d.employee_id = a.employee_id
ORDER BY d.fixed_name
SELECT d.employee_id, d.fixed_name, a.deca_id, a.status
FROM EMPLOYEE d, DECOM_ACTION a
WHERE d.employee_id = a.employee_id (+)
ORDER BY d.fixed_name
- Outer Join - with a twist
- This will select all employees without a DECOM_ACTION
SELECT d.employee_id, d.fixed_name, a.deca_id, a.status
FROM EMPLOYEE d, DECOM_ACTION a
WHERE d.employee_id = a.employee_id (+)
AND a.deca_id IS NULL
- Same thing a different way
SELECT d.employee_id, d.fixed_name
FROM EMPLOYEE d
WHERE d.employee_id NOT IN
(SELECT a.employee_id
FROM DECOM_ACTION a)
SELECT d.employee_id, d.fixed_name
FROM EMPLOYEE d
WHERE FIXED_NAME LIKE 'ms%'
AND NOT EXISTS
(SELECT 'X'
FROM DECOM_ACTION a
WHERE d.employee_id = a.employee_id)
SELECT fixed_name, DECODE(SUBSTR(fixed_name,3,1),'n','NT','t','WIN2K','u','Unix','Other') OS
FROM EMPLOYEE
SELECT DECODE(SUBSTR(fixed_name,3,1),'n','NT','t','WIN2K','u','Unix','Other') OS, COUNT(fixed_name)
FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(fixed_name,3,1),'n','NT','t','WIN2K','u','Unix','Other')
SELECT SUBSTR(fixed_name,3,1), COUNT(*)
FROM EMPLOYEE
GROUP BY SUBSTR(fixed_name,3,1)
ORDER BY COUNT(*) DESC
- Group by Having - handy way to detect duplicates
SELECT ip_address, dr_id, COUNT(*)
FROM IP_TO_DNS_RECORD
GROUP BY ip_address, dr_id
HAVING COUNT(*) > 1
DELETE FROM DEMO_EMPLOYEE
INSERT INTO DEMO_EMPLOYEE
(EMPLOYEE_ID,SYSEDGE_SIZE,INFRASTRUCTURE)
(SELECT EMPLOYEE_ID,SYSEDGE_SIZE,INFRASTRUCTURE
FROM EMPLOYEE
WHERE SUBSTR(FIXED_NAME,3,1) = 'u')
- This works every time - as long as more than one row is not returned for each employee_id
UPDATE DEMO_EMPLOYEE DEMO
SET FIXED_NAME =
(SELECT FIXED_NAME
FROM EMPLOYEE D
WHERE DEMO.EMPLOYEE_ID = D.EMPLOYEE_ID)
WHERE EXISTS
(SELECT FIXED_NAME
FROM EMPLOYEE D
WHERE DEMO.EMPLOYEE_ID = D.EMPLOYEE_ID)
- Now insert a row that doesn't exist in employee
INSERT INTO DEMO_EMPLOYEE
(EMPLOYEE_ID, SYSEDGE_SIZE, INFRASTRUCTURE,FIXED_NAME)
VALUES
(-1000, 1000,1000,'TEST')
- Without the correlating clause the wrong rows get updated:
UPDATE DEMO_EMPLOYEE DEMO
SET FIXED_NAME =
(SELECT FIXED_NAME
FROM EMPLOYEE D
WHERE DEMO.EMPLOYEE_ID = D.EMPLOYEE_ID)
SELECT EMPLOYEE_ID, FIXED_NAME FROM DEMO_EMPLOYEE WHERE EMPLOYEE_ID = -1000
SELECT EMPLOYEE_ID, COUNT(*)
FROM DECOM_ACTION
GROUP BY EMPLOYEE_ID
SELECT D.EMPLOYEE_ID, D.FIXED_NAME, S.N
FROM EMPLOYEE D,
(SELECT EMPLOYEE_ID, COUNT(*) N
FROM DECOM_ACTION
GROUP BY EMPLOYEE_ID) S
WHERE D.EMPLOYEE_ID = S.EMPLOYEE_ID
AND s.n != 33
- CROSS PRODUCT JOIN - THIS IS A BAD THING
SELECT COUNT(*) FROM NETWORK
SELECT COUNT(*) FROM LOCATION
- No join condition between Network and Location
SELECT N.NET_ID, N.LOC_ID, N.DESCRIPTION
FROM NETWORK N, LOCATION L
ORDER BY N.NET_ID, N.DESCRIPTION
SELECT N.NET_ID, N.LOC_ID, N.DESCRIPTION, L.NAME
FROM NETWORK N, LOCATION L
ORDER BY N.NET_ID, N.DESCRIPTION
- Don't be fooled by:
- You will get the correct result set but as the data grows it will run exponentially slower
SELECT UNIQUE N.NET_ID, N.LOC_ID, N.DESCRIPTION
FROM NETWORK N, LOCATION L
ORDER BY N.NET_ID, N.DESCRIPTION
- Rownum - computed at runtime - can be convoluted
SELECT ROWNUM rn, d.FIXED_NAME, EMPLOYEE_ID
FROM EMPLOYEE D
ORDER BY FIXED_NAME
WHERE ROWNUM < 20
SELECT S.FIXED_NAME,S.*
FROM
(SELECT ROWNUM RN, DEV.*
FROM
(SELECT D.*
FROM EMPLOYEE D
ORDER BY FIXED_NAME) DEV
) S
WHERE S.RN BETWEEN 300 AND 310
ORDER BY S.RN
- Combining decode, summary and group by to get metrics
SELECT TO_CHAR(SUBMIT_DATE,'YYYY-MM') MONTH ,
SUM(DECODE(RISK,0,1,0)) LOW,
SUM(DECODE(RISK,1,1,0)) MODERATE,
SUM(DECODE(RISK,2,1,0)) HIGH,
SUM(DECODE(RISK,3,1,0)) EMERGENCY,
SUM(1) TOTAL
FROM ETICKET
GROUP BY TO_CHAR(SUBMIT_DATE,'YYYY-MM')
- Union, Intersect and Minus
( SELECT d.fixed_name, u.userid,utd.username
FROM USERS u, USERS_TO_EMPLOYEE utd, EMPLOYEE d
WHERE u.u_id = utd.u_id
AND d.employee_id = utd.employee_id
AND utd.ls_priv = 1
AND U.USERID = 'pjauquet'
UNION
SELECT d.fixed_name, u.userid,utdc.username
FROM EMPLOYEE_CLASS dc, USERS u, USERS_TO_EMPLOYEE_CLASS utdc, EMPLOYEE d
WHERE d.dc_id = dc.dc_id
AND d.dc_id = utdc.dc_id
AND ( dc.name LIKE 'win%'
OR dc.name LIKE 'solaris%'
OR dc.name LIKE 'hpux%'
OR dc.name = 'freebsd'
OR dc.name = 'linux'
OR dc.name = 'dynix'
OR dc.name = 'firewall')
AND u.u_id = utdc.u_id
AND utdc.ls_priv = 1
AND U.USERID = 'pjauquet'
UNION
SELECT d.fixed_name, u.userid, utcs.username
FROM EMPLOYEE d, EMPLOYEE_CLASS dc, CLIENT_SYSTEM_TO_EMPLOYEE cs,
USERS u, USERS_TO_CLIENT_SYSTEM utcs
WHERE d.employee_id = cs.employee_id
AND cs.cs_id = utcs.cs_id
AND u.u_id = utcs.u_id
AND d.dc_id = dc.dc_id
AND utcs.ls_priv = 1
AND ( dc.name LIKE 'win%'
OR dc.name LIKE 'solaris%'
OR dc.name LIKE 'hpux%'
OR dc.name = 'freebsd'
OR dc.name = 'linux'
OR dc.name = 'dynix'
OR dc.name = 'firewall')
AND u.userid = 'pjauquet')
MINUS
SELECT d.fixed_name, u.userid,utd.username
FROM USERS u, USERS_TO_EMPLOYEE utd, EMPLOYEE d
WHERE u.u_id = utd.u_id
AND d.employee_id = utd.employee_id
AND utd.ls_priv = 0
AND U.USERID = 'pjauquet'
select ksppinm from x$ksppi where ksppinm like '%_io_%' ESCAPE '';
- Look up production state at a given point in time:
SELECT fixed_name, ps.name, NVL(psh.modified_date,created)
FROM employee d, production_state ps,
(SELECT employee_id, MAX(modified_date) modified_date
FROM production_state_hist
WHERE TRUNC(modified_date) <= TO_DATE('05/17/2004','MM/DD/RRRR')
GROUP BY EMPLOYEE_ID) psh
WHERE D.PS_ID = PS.PS_ID
AND D.EMPLOYEE_ID = PSH.EMPLOYEE_ID (+)
- Most recent record in a history table:
SELECT project_id, MAX(effdt)
FROM sysadm.ps_project_status@fin80
WHERE project_id = '1000001900'
GROUP BY project_id
- same, but joined with another table:
- examples of fixing up a query:
- BAD (over 5 min)
select fullname
from users
where u_id not in
(select unique(u_id) from ijump_reference)
and active_flag=1
order by lower(fullname)
select fullname
from users
where active_flag=1
minus
select fullname
from users u,ijump_reference i
where u.u_id = i.u_id
order by 1
select fullname
from users u,ijump_reference i
where u.u_id = i.u_id(+)
and i.u_id is null
group by fullname
select fullname
from users u
where not exists
(select 'X' from ijump_reference i where u.u_id = i.u_id)
and active_flag=1
order by lower(fullname)
NOTE: in some circumstances, GOOD, BETTER and BEST might be different, depending on the tables and the amount of data in each
v$session contains session information for that oracle instance
and you have to be oracleadmin to look at that table
MAX and GROUP BY
Question: I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns:
user_name, report_job_id, report_name, report_run_date.
Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last.
My initial query:
SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY report_name
runs fine. However, it does not provide the name of the user who ran the report.
Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run.
Any suggestions?
Answer: This is where things get a bit complicated. The SQL statement below will return the results that you want:
SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;
Let's take a few moments to explain what we've done.
First, we've aliased the first instance of the report_history table as rh.
Second, we've included two components in our FROM clause. The first is the table called report_history (aliased as rh). The second is a select statement:
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults.
Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.
Comments (0)
You don't have permission to comment on this page.