Statistics on a Table

Query to find out Index on Table

SELECT OWNER,
TABLE_NAME,
COUNT(*) INDEX_COUNT
FROM DBA_INDEXES
where OWNER not in ('SYS','SYSTEM')
and table_name = 'Tabel Name'
group by OWNER, TABLE_NAME

Query to find out column of an Index

SELECT TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
INDEX_NAME from dba_ind_columns WHERE TABLE_NAME = 'Table name'

Query to find out when the table was last analyzed and by what percentage

SELECT
NUM_ROWS,
SAMPLE_SIZE,
ROUND((SAMPLE_SIZE/NUM_ROWS)*100) "SAMPLE%",
TO_CHAR(last_analyzed,'DD-MM-YYYY:HH24:MI:SS') "Last_Analyzed"
FROM dba_tables
WHERE table_name ='Table_name';

List of objects in a PeopleSoft Project

Execute the Below Query in oracle database to get the list of objects in a PeopleSoft Project


SELECT projectname, objecttype, objectvalue1, objectvalue2, objectvalue3,
objectvalue4,
DECODE (sourcestatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS SOURCE,
DECODE (targetstatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS TARGET,
DECODE (upgradeaction,
0, 'Copy',
1, 'Delete',
2, 'None',
3, 'CopyProp'
) AS ACTION,
DECODE (takeaction, 0, 'No', 1, 'Yes') AS UPGRADE
FROM psprojectitem
WHERE projectname = 'Name of project'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8