snipt

Ctrl+h for KB shortcuts

SQL

Find Grants in tables and views in Oracle

1
2
3
select * from table_privileges 
where grantor = 'schema-name' or grantor = 'schema-name' 
order by table_name asc
https://snipt.net/embed/ec83bb0727088a263737e26ea900b6a8/
/raw/ec83bb0727088a263737e26ea900b6a8/
ec83bb0727088a263737e26ea900b6a8
sql
SQL
3
2019-07-06T17:16:25
True
False
False
Nov 11, 2012 at 11:32 PM
/api/public/snipt/50058/
find-grants-in-tables-and-views-in-oracle
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">table_privileges</span> </span><span id="L-2"><a name="L-2"></a><span class="k">where</span> <span class="n">grantor</span> <span class="o">=</span> <span class="s1">&#39;schema-name&#39;</span> <span class="k">or</span> <span class="n">grantor</span> <span class="o">=</span> <span class="s1">&#39;schema-name&#39;</span> </span><span id="L-3"><a name="L-3"></a><span class="k">order</span> <span class="k">by</span> <span class="k">table_name</span> <span class="k">asc</span> </span></pre></div> </td></tr></table>
Oracle

SQL

See all Oracle errors

select * from all_errors;
https://snipt.net/embed/2e897cd212002978bf945cf925da1946/
/raw/2e897cd212002978bf945cf925da1946/
2e897cd212002978bf945cf925da1946
sql
SQL
1
2019-07-23T02:41:26
True
False
False
Nov 11, 2012 at 11:31 PM
/api/public/snipt/50057/
see-all-oracle-errors
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="n">all_errors</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle

SQL

Find tables and views by name

1
2
3
select table_name from user_tables;

select view_name from user_views;
https://snipt.net/embed/36ae02198111240cf0b6c2937deb548e/
/raw/36ae02198111240cf0b6c2937deb548e/
36ae02198111240cf0b6c2937deb548e
sql
SQL
3
2019-07-10T10:07:52
True
False
False
Nov 11, 2012 at 11:31 PM
/api/public/snipt/50056/
find-tables-and-views-by-name
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> <span class="k">table_name</span> <span class="k">from</span> <span class="n">user_tables</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="k">select</span> <span class="n">view_name</span> <span class="k">from</span> <span class="n">user_views</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle

SQL

Find a text occurrence in Oracle PL/SQL code

1
2
3
SELECT name, line, text
  FROM user_source
 WHERE text LIKE '%someText1%' OR text LIKE '%someText2%';
https://snipt.net/embed/b620a78449db869f5fa16425623bdbd0/
/raw/b620a78449db869f5fa16425623bdbd0/
b620a78449db869f5fa16425623bdbd0
sql
SQL
3
2019-07-16T04:13:28
True
False
False
Nov 11, 2012 at 11:30 PM
/api/public/snipt/50055/
find-a-text-occurrence-in-oracle-plsql-code
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">line</span><span class="p">,</span> <span class="nb">text</span> </span><span id="L-2"><a name="L-2"></a> <span class="k">FROM</span> <span class="n">user_source</span> </span><span id="L-3"><a name="L-3"></a> <span class="k">WHERE</span> <span class="nb">text</span> <span class="k">LIKE</span> <span class="s1">&#39;%someText1%&#39;</span> <span class="k">OR</span> <span class="nb">text</span> <span class="k">LIKE</span> <span class="s1">&#39;%someText2%&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
Oracle