Credit: Most of this document was written by a co-worker, Evelyn Dibben. Thanks Evelyn!
alter view <view name> compile;
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.
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
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;
select * from dba_db_links
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);
INSERT INTO TEST_DATES (D1, D2, D3) VALUES (SYSDATE, TRUNC(SYSDATE), TO_DATE('17-MAR-2001','DD-MON-RRRR')) COMMIT;
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
SELECT * FROM TEST_DATES WHERE D1 = D2
SELECT * FROM TEST_DATES WHERE TRUNC(D1) = TRUNC(D2)
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
SELECT D1,D2,D3,D1 - D2, D2-D3 FROM TEST_DATES
SELECT (D1-D2) * 86400 FROM TEST_DATES
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
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
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
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')
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)
INSERT INTO DEMO_EMPLOYEE (EMPLOYEE_ID, SYSEDGE_SIZE, INFRASTRUCTURE,FIXED_NAME) VALUES (-1000, 1000,1000,'TEST')
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
SELECT COUNT(*) FROM NETWORK SELECT COUNT(*) FROM 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
SELECT UNIQUE N.NET_ID, N.LOC_ID, N.DESCRIPTION FROM NETWORK N, LOCATION L ORDER BY N.NET_ID, N.DESCRIPTION
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
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')
( 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 '';
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 (+)
SELECT project_id, MAX(effdt) FROM sysadm.ps_project_status@fin80 WHERE project_id = '1000001900' GROUP BY project_id
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
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.