Some useful query for Oracle DBA advance to novice
/*
To show primary/foreign key relationships of all tables and views in a given schema
*/
SELECT D.TABLE_NAME "Table name",
D.CONSTRAINT_NAME "Constraint name",
DECODE (D.CONSTRAINT_TYPE,
'P', 'Primary Key',
'R', 'Foreign Key',
'C', 'Check/Not Null',
'U', 'Unique',
'V', 'View Cons')
"Type",
D.SEARCH_CONDITION "Check Condition",
P.TABLE_NAME "Ref Table name",
P.CONSTRAINT_NAME "Ref by",
M.COLUMN_NAME "Ref col",
M.POSITION "Position",
P.OWNER "Ref owner"
FROM DBA_CONSTRAINTS D
LEFT JOIN DBA_CONSTRAINTS P
ON (D.R_OWNER = P.OWNER AND D.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME)
LEFT JOIN DBA_CONS_COLUMNS M
ON (D.CONSTRAINT_NAME = M.CONSTRAINT_NAME)
WHERE D.TABLE_NAME IN (SELECT TABLE_NAME
FROM DBA_TABLES
WHERE OWNER = UPPER ('STLBAS')
UNION ALL
SELECT VIEW_NAME
FROM DBA_VIEWS
WHERE OWNER = UPPER ('STLBAS'))
ORDER BY 1, 2, 3
/*
To see all objects in a table space file
*/
SELECT D.OWNER,
D.SEGMENT_NAME,
D.SEGMENT_TYPE,
D.TABLESPACE_NAME,
D.HEADER_FILE,
V.NAME
FROM Dba_Segments D JOIN V$DATAFILE V ON (D.HEADER_FILE = V.FILE#)
WHERE D.OWNER IN ('STLBAS')
/* To see size of your tables and indexes) */
SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES
FROM USER_SEGMENTS
WHERE segment_type IN ('TABLE', 'INDEX')
/* To see free spaces in table spaces */
SELECT b.tablespace_name,
b."Total MB",
ROUND (b."Total MB" - a."Free MB", 2) "Used MB",
a."Free MB",
ROUND ( ( (b."Total MB" - a."Free MB") / b."Total MB") * 100, 2)
"% used"
FROM ( ( SELECT f.tablespace_name,
ROUND (SUM (f.bytes / (1024 * 1024)), 2) "Free MB"
FROM dba_free_space f
GROUP BY f.tablespace_name) a
RIGHT JOIN
( SELECT d.tablespace_name,
ROUND (SUM (d.bytes / (1024 * 1024)), 2) "Total MB"
FROM dba_data_files d
GROUP BY d.tablespace_name) b
ON a.tablespace_name = b.tablespace_name)
ORDER BY 1
/* Which user executing what type of command */
SELECT SID,
SERIAL#,
v.schemaname,
DECODE (COMMAND,
0, 'None',
2, 'Insert',
3, 'Select',
6, 'Update',
7, 'Delete',
8, 'Drop',
26, 'Lock Table',
44, 'Commit',
45, 'Rollback',
47, 'PL/SQL Execute',
'Other')
command
FROM V$SESSION v
-- cheers !!!
Comments
Post a Comment