SELECT table_schema
GRANTOR, PRIVILEGE
FROM all_TAB_PRIVS
WHERE GRANTEE = 'PUBLIC'
AND GRANTOR <> 'SYS'
AND GRANTOR <> 'SYSTEM'
and table_schema = 'SYSADM'
order by 1,2,3,4;
SELECT (A.EMPLID)"GPID",
(SELECT PED.ALTER_EMPLID FROM SYSADM.PS_PERS_DATA_EFFDT PED WHERE PED.EMPLID = A.EMPLID AND
PED.EFFDT = (SELECT MAX(PED1.EFFDT) FROM SYSADM.PS_PERS_DATA_EFFDT PED1 WHERE PED1.EMPLID =PED.EMPLID)) "GID",
(A.DT_OF_DEATH)"DATE OF DEATH",B.HR_STATUS
,B.EMPL_RCD,B.EFFDT,B.EFFSEQ,B.ACTION,B.ACTION_REASON,B.REG_REGION
FROM SYSADM.PS_CURRENT_JOB B, SYSADM.PS_PERSON A
WHERE A.EMPLID = B.EMPLID
AND B.REG_REGION IN('AUS','NZL')
AND TRIM(A.DT_OF_DEATH) IS NOT NULL;
--The number of users connected on the environment at the moment
select count(Nr) USERS_NUMBER FROM ( SELECT count(OPRID) as Nr FROM PSACCESSLOG where to_date(sysdate)-to_date(LOGINDTTM)>=0 and to_date(LOGOUTDTTM)-to_date(sysdate)>=0 group by OPRID)
--The users connected on the environment at the moment
select DISTINCT OPRID, LOGIPADDRESS, TO_CHAR(LOGINDTTM ,'YYYY-MM-DD:hh:mi:ss') LOGINTIME,TO_CHAR(LOGOUTDTTM ,'YYYY-MM-DD:hh:mi:ss') LOGOUTIME, TO_CHAR((sysdate),'YYYY-MM-DD:hh:mi:ss') CURRTIME FROM PSACCESSLOG where to_date(sysdate)-to_date(LOGINDTTM)>=0 and to_date(LOGOUTDTTM)-to_date(sysdate)>=0
SELECT DISTINCT P.LABEL_ID,P.LBLTEXT,P.FIELDNAME,P.RECNAME,F.LENGTH
,(CASE WHEN F.FIELDTYPE = '0' THEN 'Character'
WHEN F.FIELDTYPE='1' THEN 'Long Character'
WHEN F.FIELDTYPE='2' THEN 'Number'
WHEN F.FIELDTYPE='3' THEN 'Signed number'
WHEN F.FIELDTYPE='4' THEN 'Date'
WHEN F.FIELDTYPE='5' THEN 'Time'
WHEN F.FIELDTYPE='6' THEN 'DateTime'
WHEN F.FIELDTYPE='8' THEN 'Attachment'
ELSE 'TBD'
END) as Field_Type
FROM SYSADM.PSPNLFIELD P,SYSADM.PSDBFIELD F
WHERE P.PNLNAME='JOB_DATA1'
AND P.FIELDNAME=F.FIELDNAME
ORDER BY P.LBLTEXT;
-- Disciplinary Actions by Reg_region,employee type
SELECT X2.*,X1.DESCR FROM SYSADM.PS_DISCIP_TYPE_TBL X1,(SELECT
(SELECT JB.REG_REGION FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)REG_REGION,
(SELECT JB.EMPL_TYPE FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)EMPLOYEE_TYPE,
A.DISCIPLINE_DT,A.EMPLID,A.PURGE_DT, A.DISCIPLINARY_TYPE
FROM SYSADM.PS_DISCIPLIN_ACTN A ORDER BY 1,2,3 ASC)X2 WHERE X2.DISCIPLINARY_TYPE = X1.DISCIPLINARY_TYPE AND X2.REG_REGION = X1.REG_REGION
AND X1.EFFDT =(SELECT MAX(X3.EFFDT) FROM SYSADM.PS_DISCIP_TYPE_TBL X3 WHERE X1.DISCIPLINARY_TYPE = X3.DISCIPLINARY_TYPE AND
X3.REG_REGION = X1.REG_REGION AND X3.EFFDT < SYSDATE);
Below SQL will return all the records that have PER_ORG_ASGN as parent record in record properties in Application Designer.
select RECNAME from PSRECDEFN where PARENTRECNAME = 'PER_ORG_ASGN'
Apart from the above direct child records, we will also be interested to know indirect child records. For example PER_ORG_ASGN is parent of JOB and JOB is parent of JOB_JR. To understand the complete hierarchy (i.e) direct and indirect child records, you can use the below SQL.
select ltrim(C.ChildPath,' > ') "Parent > Child" from
(select RECNAME,PARENTRECNAME,SYS_CONNECT_BY_PATH(RECNAME, ' > ') as ChildPath
from PSRECDEFN
start with RECNAME = 'PER_ORG_ASGN'
connect by PRIOR RECNAME = PARENTRECNAME) C
where RECNAME <> 'PER_ORG_ASGN
create database link "database_link_name"
connect to username
identified by "password"
using 'connection_name';
select distinct db_link, created from dba_db_links where db_link='DB LINK NAME';
These views are helpful for working with DB links in Oracle:
DBA_DB_LINKS - All DB links defined in the database
ALL_DB_LINKS - All DB links the current user has access to
USER_DB_LINKS - All DB links owned by current user
