SELECT owner,
SUM (DECODE (NVL (num_rows, 9999999), 9999999, 0, 1)) analyzed,
SUM (DECODE (NVL (num_rows, 9999999), 9999999, 1, 0)) not_analyzed,
COUNT (table_name) total
FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner;
SELECT table_name,
TO_CHAR (last_analyzed, 'MM/DD/YYYY HH24:MI:SS') last_analyzed
FROM user_tab_columns
WHERE last_analyzed IS NOT NULL
AND column_id = 1
AND (SYSDATE - last_analyzed) < 30
ORDER BY 2;
select floor(((end_time - start_time)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((end_time - start_time)*24*60*60) -
floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((end_time - start_time)*24*60*60) -
floor(((end_time - start_time)*24*60*60)/3600)*3600 -
(floor((((end_time - start_time)*24*60*60) -
floor(((end_time - start_time)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
from sub_email_jobs;
Public Sub Convert_To_Hyperlinks()
Dim Cell As Range
For Each Cell In Intersect(Selection, ActiveSheet.UsedRange)
If Cell <> "" Then
ActiveSheet.Hyperlinks.Add Cell, Cell.Value
End If
Next
End Sub
Sub RemoveHyperlinks() 'Remove all hyperlinks from the active sheet ActiveSheet.Hyperlinks.Delete End Sub
SELECT SUBSTR (df.tablespace_name, 1, 20) "Tablespace Name",
SUBSTR (df.file_name, 1, 40) "File Name",
ROUND (df.bytes / 1024 / 1024, 2) "Size (M)",
ROUND (e.used_bytes / 1024 / 1024, 2) "Used (M)",
ROUND (f.free_bytes / 1024 / 1024, 2) "Free (M)",
RPAD (' ' || RPAD ('X', ROUND (e.used_bytes * 10 / df.bytes, 0), 'X'),
11,
'-')
"% Used"
FROM dba_data_files df,
(SELECT file_id, SUM (DECODE (bytes, NULL, 0, bytes)) used_bytes
FROM dba_extents
GROUP BY file_id) e,
(SELECT MAX (bytes) free_bytes, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id = df.file_id AND df.file_id = f.file_id
ORDER BY df.tablespace_name, df.file_name;
SELECT SUBSTR (df.name, 1, 40) file_name,
df.bytes / 1024 / 1024 allocated_mb,
( (df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id,
df.name,
df.file#,
df.bytes
ORDER BY file_name;
SELECT table_name
FROM (SELECT table_name FROM user_tables
MINUS
SELECT table_name FROM user_indexes) orasnap_noindex
WHERE table_name LIKE 'HSO%'
ORDER BY table_name
UPDATE a
SET data =
(SELECT data
FROM b
WHERE id1 = a.id1 AND id2 = a.id2)
WHERE EXISTS
(SELECT 1
FROM b
WHERE id1 = a.id1 AND id2 = a.id2);
Example two, can be used for multiple columns
UPDATE (SELECT a.data a_data, b.data b_data
FROM a, b
WHERE a.id1 = b.id1 AND a.id2 = b.id2)
SET a_data = b_data;
DELETE FROM customers
WHERE id IN (SELECT id
FROM (SELECT id,
lastname,
firstname,
RANK ()
OVER (PARTITION BY lastname, firstname
ORDER BY id)
AS seqnumber
FROM customers)
WHERE seqnumber > 1);
DELETE FROM table_name
WHERE ROWID NOT IN
(SELECT MAX (ROWID)
FROM TABLE
GROUP BY duplicate_values_field_name);
SELECT *
FROM moon.sub_globals
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
subscriber_id,
sub_member_id,
RANK ()
OVER (
PARTITION BY subscriber_id, sub_member_id
ORDER BY ROWID)
AS seqnumber
FROM moon.sub_globals) a
WHERE seqnumber > 1)
MS Project is a good tool for managing project costs but it can be a bit tricky if you are trying to pull information from it for reporting.
For the projects I manage, each task has a cost category: Deferred and Expense. This is a custom column created to reflect Capex vs. Opex costs for the project.
I was trying to find a way to pull a Cash Flow (Cost month-by-month) report for the two Cost Categories which showed my total costs on the project like so:
Cost Category | January | February | March | April | May | June | July |
Deferred SW | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx |
Expense | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx | xx.xx |
At first I was trying to play around with Views that showed me the information in the format I wanted and then export it in Excel. This obviously didn’t work. Getting project to filter by the Cost Category column wasn’t easy and the Summary Totals reflected a sum of the two categories.
That’s when I came across the Visual Reports feature of Project.
This report allowed me to choose the extra column I wanted (Cost Category) and create a pivot table in Excel which I could modify as needed.
Got exactly what I was looking for.
Sub AddToContactsFromEmail()
Dim olkContacts As MAPIFolder, olkContact As ContactItem, olkSelected As Selection, olkItem As Object, olkRecipient As Recipient
'Select the email in outlook from which you want the contacts added
Set olkSelected = Application.ActiveExplorer.Selection
If olkSelected.Count > 0 Then
For Each olkItem In olkSelected
If olkItem.Class = olMail Then
Set olkContacts = Application.Session.GetDefaultFolder(olFolderContacts)
For Each olkRecipient In olkItem.Recipients
Set olkContact = Application.CreateItem(olContactItem)
With olkContact
.FullName = olkRecipient.Name
.Email1Address = olkRecipient.Address
.BusinessAddress = olkRecipient.AddressEntry.GetExchangeUser.Address
.OfficeLocation = olkRecipient.AddressEntry.GetExchangeUser.OfficeLocation
.BusinessTelephoneNumber = olkRecipient.AddressEntry.GetExchangeUser.BusinessTelephoneNumber
.JobTitle = olkRecipient.AddressEntry.GetExchangeUser.Alias
'Enter additional properties required from GAL
.Categories = "Exported"
.Save
End With
Next
End If
Next
End If
Set olkContact = Nothing
Set olkContacts = Nothing
Set olkSelected = Nothing
Set olkItem = Nothing
Set olkRecipient = Nothing
End Sub