Re-compiling a view Work with procedures in TOAD Undo Segment Misc See all the DB links Null values MAX and GROUP BY

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

• Convert days to seconds

 

 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')

• Inner Join

 

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

 

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)

• Same thing a better way

 

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)

• Decode Statement

 

 SELECT fixed_name,  DECODE(SUBSTR(fixed_name,3,1),'n','NT','t','WIN2K','u','Unix','Other') OS
 FROM EMPLOYEE

• Group by

 

 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 

• Correlated update

 

DELETE FROM DEMO_EMPLOYEE

• Insert from a table

 

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  

• INLINE VIEW

 

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) 

• GOOD (7 sec)

 

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

• BETTER (2 sec)

 

select fullname
from users u,ijump_reference i
where u.u_id = i.u_id(+)
and i.u_id is null
group by fullname

• BEST (instant)

 

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.