Can a table and a materialized view have the same name in one schema in a DB?

A materialized view is a table, therefore you'd expect to see both of the entries in DBA_OBJECTS. If you have created a materialized view in a schema, then you can't create a table in the same schema with the same name as the materialized view.

Code:

SQL> drop materialized view test_master;

Materialized view dropped.

SQL> drop table test_master;
drop table test_master

           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE MATERIALIZED VIEW test_master

  2     BUILD IMMEDIATE
  3     AS SELECT object_name, object_type
  4     from dba_objects;

Materialized view created.

SQL> select owner,
  2     object_name,
  3     object_type,
  4     status
  5  from dba_objects
  6  where object_name='TEST_MASTER';

OWNER    OBJECT_NAME                    OBJECT_TYPE         STATUS
-------- ------------------------------ ------------------- -------
TEST     TEST_MASTER                    TABLE               VALID
TEST     TEST_MASTER                    MATERIALIZED VIEW   VALID

SQL> create table test_master (a1 number);
create table test_master (a1 number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

Queries to get Oracle Database Object Information

Tables

This is a query to get all Oracle tables that can be viewed by the current user.

select TABLE_NAME, OWNER from SYS.ALL_TABLES order by OWNER, TABLE_NAME 

The query can be filtered to return tables for a given schema by adding a where OWNER = 'some_schema' clause to the query.

·          select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE  from   ALL_TAB_COLS where OWNER in ('DW_ETL_USER','DW_STAGING','REPORT') order by OWNER, TABLE_NAME ,COLUMN_NAME;

·          select object_name, OBJECT_TYPE from SYS.ALL_OBJECTS;

·          SELECT * FROM USER_OBJECTS;

·          select ALL_TAB_COLS.OWNER,ALL_TAB_COLS.TABLE_NAME,ALL_OBJECTS.object_TYPE,ALL_TAB_COLS.COLUMN_NAME,ALL_TAB_COLS.DATA_TYPE,

ALL_TAB_COLS.DATA_LENGTH,ALL_TAB_COLS.NULLABLE  from   ALL_TAB_COLS,SYS.ALL_OBJECTS where ALL_OBJECTS.object_name=ALL_TAB_COLS.TABLE_NAME

and ALL_OBJECTS.OWNER=ALL_TAB_COLS.OWNER

and ALL_TAB_COLS.OWNER in ('STAGING','REPORT')

and ALL_OBJECTS.object_TYPE ='TABLE'  order by ALL_TAB_COLS.OWNER,ALL_TAB_COLS.TABLE_NAME,ALL_OBJECTS.OBJECT_TYPE,ALL_TAB_COLS.COLUMN_NAME;

Schemas

This is a query to get all Oracle schemas in an Oracle database instance.

select USERNAME from SYS.ALL_USERS order by USERNAME

Views

This is a query to get all Oracle views that can be viewed by the current user.

select VIEW_NAME, OWNER from SYS.ALL_VIEWS order by OWNER, VIEW_NAME 

The query can be filtered to return views for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Packages

This is a query to get all Oracle packages that can be viewed by the current user.

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'PACKAGE' order by OWNER, OBJECT_NAME 

To query for package bodies, substitute PACKAGE BODY for PACKAGE.

The query can be filtered to return packages for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Procedures

This is a query to get all Oracle procedures that can be viewed by the current user.

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('PROCEDURE') order by OWNER, OBJECT_NAME

The query can be filtered to return procedures for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Procedure Columns

This is a query to get the columns in an Oracle procedure.

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from SYS.ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE

Functions

This is a query to get all Oracle functions for the current user.

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) = upper('FUNCTION') order by OWNER, OBJECT_NAME 

The query can be filtered to return functions for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Triggers

This is a query to get all Oracle triggers for the current user.

select TRIGGER_NAME, OWNER from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME 

The query can be filtered to return triggers for a specific schema by adding a where OWNER = 'some_schema' clause to the query.

Indexes

This is a query to get all Oracle indexes.

select INDEX_NAME, TABLE_NAME, TABLE_OWNER from SYS.ALL_INDEXES order by TABLE_OWNER, TABLE_NAME, INDEX_NAME