Tables used by materialized views in a schema

Posted on September 24, 2015 by kuperusm

Query to show all tables used (and how many times) by your materialized views in a schema
SELECT ad.detailobj_owner , ad.detailobj_name , count(1) as occurrence FROM all_mview_detail_relations ad WHERE ad.owner = 'YOURSCHEMA' GROUP BY ad.detailobj_owner, ad.detailobj_name ORDER BY ad.detailobj_owner, ad.detailobj_name

Remove text part in script with regular expression in Notepad++

Posted on September 3, 2015 by kuperusm

When dealing with Oracle create table scripts, sometimes it’s useful to remove the ‘tablespace USERS…’ part from the script. Using a regular expression (in Notepad++) it’s easy.

Find what: tablespace.*?; Replace with: ; Wrap around: checked Regular expression: checked . matchesnewline: checked

Works with tablespace clause on indices as well