How to differentiate between user-defined public synonyms and built-in public synonyms

Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.

By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:

SQL - Structured Query Language

PL/SQL - Procedural Language / Structured Query Language

The observant reader might notice how SQL shows up twice 8) That's because SQL is often embedded within PL/SQL - PL/SQL is a language that was made to provide a proprietary 4th generation language (4GL) that plays very well with database objects in Oracle.

Wikipedia has some pretty good material on both SQL and PL/SQL

The confusing part is where PL/SQL and SQL overlap a bit. SQL's purview includes data insert, query, update and delete, the so-called DML, or data manipulation language operations, but it also includes create, alter, rename, drop which are DDL or data definition language operations. Its here where some might get confused. The operation to create a stored procedure, something written using PL/SQL, is actually SQL - you use SQL to create the database object that represents a block of PL/SQL.

Likewise, you can embed SQL code inside your PL/SQL. A FOR loop in PL/SQL can be based upon a SQL query, for example. Blows your mind a little, eh? You create a procedure using SQL that actually internally uses SQL to perform some action on records from the database.

Cool stuff if you ask me.

Specify the owner in GET_DDL, or use ALL_MVIEWS instead of USER_MVIEWS:

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MVIEW_NAME', 'OWNER') from dual;

select query from all_mviews
  where mview_name = 'your materialized view';