==========================
Oracle SQL

Oracle Version = select banner from v$version where banner like '%racle%'
Database Status = select database_status from v$instance
Shared Pool Size = select sum(getmisses)/sum(gets)*100 pool_size from v$rowcache
Memory Sort Rate = select round((sum(decode(name, 'sorts (memory)', value, 0)) / (sum(decode(name, 'sorts (memory)', value, 0)) + sum(decode(name, 'sorts (disk)', value, 0)))) * 100,2) \"Sort in Memory %\" from v$sysstat
Tablespace Status = select tablespace_name, status from dba_tablespaces
Background Processes = select NAME,  DESCRIPTION from v$bgprocess
Session Info = select SCHEMANAME, OSUSER, MACHINE, PROGRAM from V$SESSION where SCHEMA# > 0
==========================

IntraLink SQL

pdm.pdm_pool = select * from pdm.pdm_pool
pdm.pdm_fileserver = select * from pdm.pdm_fileserver
pdm.pdm_releaselevel = select * from pdm.pdm_releaselevel
pdm.pdm_releasescheme = select * from pdm.pdm_releasescheme
pdm.pdm_folder = select * from pdm.pdm_folder
pdm.pdm_productitem = select * from pdm.pdm_productitem
pdm.pdm_productitemversion = select * from pdm.pdm_productitemversion
pdm.pdm_pivrl = select * from pdm.pdm_pivrl

pdm.pdm_lovfile = select * from pdm.pdm_lovfile
==========================

How easy is it to write a SQL script that would go through all table data
and rename any instance of release level "WIP" with "Draft"? We are using
3.2 SP2 on an windows 2000 server.

SQL PLUS <ENTER>
SYSTEM <ENTER>
login as a manager
DESC PDM.PDM_RELEASELEVEL;<ENTER>
UPDATE PDM.PDM_RELEASELEVEL='new RL name' WHERE RLNAME='old RL name';
<ENTER>
COMMIT; <ENTER>

==========================

==========================

==========================

==========================

==========================

For Intralink 3.0, this can be determined even if the History mechanism
is not enabled.  Since Intralink tracks both current and original names
upon a rename, a report can be generated to show where the names differ,
but it's a little complicated.

Renames can occurs in both the Commonspace and the Workspace (if imported
and renamed).   Family table generics, instances, and non family table
objects have different arrangements for the storage of the original names,
which means that three basic searches are necessary.


The following sql queries will identify and show all versions of all renamed
objects - basically a rename history.  In some scenarios, different versions
of an object could have different original names, and/or the most recent
version may have the same current and original names.  In other scenarios,
the same version of a renamed instance may appear more than once.


+ Non Family Table Object Renames:

set linesize 120
column PINAME format a35 heading 'New_Object_Name'
column PIVFNAME format a35 heading 'Old_Object_Name'
column BRPATH format a10
column PIVREV format a6
select
ren.PINAME,pivf.PIVFNAME,br.BRPATH,piv.PIVREV,piv.PIVVER
from
(select
distinct pi.PINAME
from
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_BRANCH br,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_PRODUCTITEMVERFILE pivf
where
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=pivf.PIVID and
pi.PINAME!=pivf.PIVFNAME
group by PINAME
) ren,
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_BRANCH br,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_PRODUCTITEMVERFILE pivf
where
pi.PINAME=ren.PINAME and
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=pivf.PIVID
order by PINAME,PIVFNAME
;


+ Family Table Generic Renames:

set linesize 120
column PINAME format a35 heading 'New_Generic_Name'
column LOVFNAME format a35 heading 'Old_Generic_Name'
column PIVREV format a6
column BRPATH format a20
select
ren.PINAME,lovf.LOVFNAME,br.BRPATH,piv.PIVREV,piv.PIVVER
from
(select
distinct pi.PINAME
from
pdm.PDM_LOVFILE lovf,
pdm.PDM_LOVCONTENTS lovc,
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_BRANCH br
where
piv.PIVCLASS=2 and
pi.PINAME!=lovf.LOVFNAME and
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=lovc.PIVID and
lovf.LOVID=lovc.LOVID
group by pi.PINAME
) ren,
pdm.PDM_LOVFILE lovf,
pdm.PDM_LOVCONTENTS lovc,
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_BRANCH br
where
pi.PINAME=ren.PINAME and
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=lovc.PIVID and
lovf.LOVID=lovc.LOVID
;


+ Family Table Instance Renames:

set linesize 120
column PINAME format a35 heading 'New_Instance_Name'
column GIRSTORINSTNAME format a35 heading 'Old_Instance_Name'
column BRPATH format a20
column PIVREV format a6
select
ren.PINAME,gir.GIRSTORINSTNAME,br.BRPATH,piv.PIVREV,piv.PIVVER
from
(select
distinct pi.PINAME
from
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_BRANCH br,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_GENINSREL gir
where
pi.PINAME!=gir.GIRSTORINSTNAME and
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=gir.INSTPIVID
group by pi.PINAME
) ren,
pdm.PDM_PRODUCTITEM pi,
pdm.PDM_BRANCH br,
pdm.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_GENINSREL gir
where
pi.PINAME=ren.PINAME and
pi.PIID=br.PIID and
piv.BRID=br.BRID and
piv.PIVID=gir.INSTPIVID
;

==========================

SELECT @PIVFNAME = 'RADU8120'

SELECT @strSQL = 'INSERT INTO ilink_config
SELECT *, NULL, NULL, @Level , NULL, NULL
FROM OPENQUERY(IPDMS, "SELECT DISTINCT 
PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME, 
PDM.PDM_PRODUCTITEMVERFILE.PIVFMAPNAME, 
PDM.PDM_PRODUCTITEMVERSION.PIVREV, 
PDM.PDM_PRODUCTITEMVERSION.PIVVER, 
PDM.PDM_PRODUCTITEM.PINAME

FROM PDM.PDM_DEPENDENCYGRAPH, 
PDM.PDM_PRODUCTITEM, 
PDM.PDM_PRODUCTITEMVERSION, 
PDM.PDM_PRODUCTITEMVERFILE
WHERE PDM.PDM_DEPENDENCYGRAPH.PIID = 
PDM.PDM_PRODUCTITEM.PIID
AND 
PDM.PDM_DEPENDENCYGRAPH.PIVID = 
PDM.PDM_PRODUCTITEMVERSION.PIVID
AND 
PDM.PDM_PRODUCTITEMVERSION.PIVID = 
PDM.PDM_PRODUCTITEMVERFILE.PIVID
AND PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME = "' 
+@PIVFNAME+'"
ORDER BY PDM.PDM_PRODUCTITEMVERFILE.PIVFNAME, 
PDM.PDM_PRODUCTITEM.PINAME, 
PDM.PDM_PRODUCTITEMVERSION.PIVREV, 
PDM.PDM_PRODUCTITEMVERSION.PIVVER")'
BEGIN
exec sp_executesql @strSQL
END

==========================

==========================

==========================