sql for introspection information about schema, tables, views and other for various database

oracle_database_schema_tables_views.sql

-- basic doc: http://docs.oracle.com/cd/B19306_01/network.102/b14266/admusers.htm#i1008832

-- get all database schema's name from Oracle Database
-- http://www.orafaq.com/wiki/List_of_default_database_users
-- http://www.adp-gmbh.ch/ora/misc/known_schemas.html
SELECT DISTINCT OWNER
FROM ALL_OBJECTS
WHERE OWNER NOT IN ('APEX_030200', 'CTXSYS', 'DBSNMP', 'EXFSYS',
                    'MDSYS', 'OLAPSYS', 'ORDDATA', 'ORDPLUGINS', 
                    'ORDSYS', 'PUBLIC', 'SYS', 'SYSTEM', 'WMSYS',
                    'XDB')



-- get all names of tables, views and synonyms from a specific schema
-- 
-- first approach
SELECT TABLE_NAME, OWNER, 't' FROM ALL_TABLES 
       WHERE OWNER = 'SCHEMA_NAME' 
             UNION ALL (SELECT VIEW_NAME, OWNER, 'v' FROM ALL_VIEWS WHERE OWNER = 'SCHEMA_NAME')
             UNION ALL (SELECT SYNONYM_NAME, OWNER, 's' FROM ALL_SYNONYMS WHERE OWNER = 'SCHEMA_NAME')

-- second approach
SELECT DISTINCT OWNER, OBJECT_NAME, OBJECT_TYPE 
  FROM ALL_OBJECTS
 WHERE (
       OBJECT_TYPE = 'TABLE' 
       OR OBJECT_TYPE = 'VIEW' 
--        OR OBJECT_TYPE = 'SYNONYM'
       )
   AND OWNER = 'SCHEMA_NAME'
   ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME

# http://dba.stackexchange.com/questions/87463/in-oracle-how-to-get-a-list-with-the-names-of-schemas-that-a-user-has-read-perm

SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%SELECT ANY TABLE%'; 
SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%CONNECT%';
SELECT * FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE like '%RESOURCE%';

# http://stackoverflow.com/questions/9811670/how-to-show-all-privileges-from-a-user-in-oracle

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

You can use the below views to find role related informations,
# DBA_ROLE_PRIVS - Roles granted to users and roles
# ROLE_ROLE_PRIVS - Roles which are granted to roles
# ROLE_SYS_PRIVS - System privileges granted to roles
# ROLE_TAB_PRIVS - Table privileges granted to roles

SELECT * FROM ROLE_ROLE_PRIVS
SELECT * FROM ROLE_SYS_PRIVS
SELECT * FROM ROLE_TAB_PRIVS
SELECT * FROM DBA_ROLE_PRIVS;

# http://dba.stackexchange.com/questions/14901/oracle-list-users-with-access-to-certain-tables


#
# get column informations
SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
{'owner': u'C_ERGON', 'table_name': u'View_FOO'}

postgresql_database_schema_tables_views.sql

-- basic doc: http://www.postgresql.org/docs/9.3/static/user-manag.html
-- why, by default, postgresql not have synonyms: http://www.postgresql.org/message-id/[email protected]
-- but can be done via paid extension for Oracle compatibility: http://www.enterprisedb.com/docs/en/9.3/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-08.htm#P1243_62877
-- https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2

-- get all database schema name from Postgresql Database
-- first approach
select schema_name, schema_owner
from INFORMATION_SCHEMA.schemata 
WHERE schema_name NOT IN ('information_schema', 'pg_catalog')
AND schema_name NOT LIKE 'pg_toast%'

-- get all views from postgresql database with schema
-- http://dba.stackexchange.com/questions/23836/how-to-list-all-views-in-sql-in-postgresql
SELECT table_name AS view_name, table_schema AS schema_name 
FROM INFORMATION_SCHEMA.views 
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
AND table_schema NOT LIKE 'pg_toast%'



-- get all tables and views from postgresql database with schemas
-- http://dba.stackexchange.com/questions/30061/how-do-i-list-all-tables-in-all-schemas-owned-by-the-current-user-in-postgresql
-- 

SELECT nsp.nspname AS object_schema,
       cls.relname AS object_name, 
       rol.rolname AS owner, 
       CASE cls.relkind
         WHEN 'r' THEN 'TABLE'
         WHEN 'i' THEN 'INDEX'
         WHEN 'S' THEN 'SEQUENCE'
         WHEN 'v' THEN 'VIEW'
         WHEN 'c' THEN 'TYPE'
         ELSE cls.relkind::text
       END AS object_type
FROM pg_class cls
  JOIN pg_roles rol ON rol.oid = cls.relowner
  JOIN pg_namespace nsp ON nsp.oid = cls.relnamespace
WHERE nsp.nspname NOT IN ('information_schema', 'pg_catalog')
  AND nsp.nspname NOT ILIKE 'pg_toast%'
   AND rol.rolname = current_user  --- remove this if you want to see all objects from all users
   AND  (cls.relkind = 'r' OR cls.relkind = 'v') --- remove this if you want to see all objects
ORDER BY nsp.nspname, cls.relname;

pycallgraph.txt

pycallgraph -v --stdlib --include "django.db.backends.oracle.*" --include "django.db.backends.base.*" graphviz -- ./manage.py inspectdb

related_django_issues_and_other_docs.txt

https://code.djangoproject.com/ticket/6148
https://code.djangoproject.com/ticket/7556
https://code.djangoproject.com/ticket/22673


I compiled a list of various implementations of multitenancy in Django:

https://github.com/pombredanne/django-simple-multitenant/blob/master/README.rst
https://github.com/tomturner/django-tenants

SQLAlchemy for Django Users: http://lucumr.pocoo.org/2011/7/19/sqlachemy-and-you/

http://stackoverflow.com/questions/18199053/example-of-what-sqlalchemy-can-do-and-django-orm-cannot
https://groups.google.com/forum/#!msg/django-developers/4jw79p76uUo/7gduK9rHeosJ%5d

http://lethain.com/replacing-django-s-orm-with-sqlalchemy/
http://techspot.zzzeek.org/2013/04/04/introduction-to-sqlalchemy-pycon-2013-wrapup/
http://www.aosabook.org/en/sqlalchemy.html

settings.py

# This is an example test settings file for use with the Django test suite.
#
# The 'sqlite3' backend requires only the ENGINE setting (an in-
# memory database will be used). All other backends will require a
# NAME and potentially authentication information. See the
# following section in the docs for more information:
#
# https://docs.djangoproject.com/en/dev/internals/contributing/writing-code/unit-tests/
#
# The different databases that Django supports behave differently in certain
# situations, so it is recommended to run the test suite against as many
# database backends as possible.  You may want to create a separate settings
# file for each of the backends you test against.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        # 'HOST': 'localhost',
        # 'PORT': server.PORT,
        'NAME': 'orcl',
        'USER': 'system',
        'PASSWORD': 'oracle',
        'TEST': {
            'USER': 'd1_u',
            'TBLSPACE': 'd1_ts',
            'TBLSPACE_TMP': 'd1_ts_tmp',
        },
    },
    'other': {
        'ENGINE': 'django.db.backends.oracle',
        # 'HOST': 'localhost',
        # 'PORT': server.PORT,
        'NAME': 'orcl',
        'USER': 'system',
        'PASSWORD': 'oracle',
        'TEST': {
            'USER': 'd2_u',
            'TBLSPACE': 'd2_ts',
            'TBLSPACE_TMP': 'd2_ts_tmp',
        },
    }
}

SECRET_KEY = "django_tests_secret_key"

# Use a fast hasher to speed up tests.
PASSWORD_HASHERS = (
    'django.contrib.auth.hashers.MD5PasswordHasher',
)

sql_alchemy_introspection.sh

##################################################
# ORACLE introspection
##################################################
# instrocpect a database view
################################
from sqlalchemy import Table, create_engine, MetaData
engine = create_engine('oracle+cx_oracle://USER:PASSWORD@IP:PORT/DATABASE_NAME', 
                       echo=True # this enable log of all sql executed from a database engine
                      )
metadata = MetaData()
funds_table = Table('DATABASE_VIEW_NAME', metadata, autoload=True, autoload_with=engine, schema="SCHEMA_NAME")
2015-02-02 09:30:42,984 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL
2015-02-02 09:30:42,984 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 09:30:42,986 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
2015-02-02 09:30:42,986 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 09:30:42,989 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
2015-02-02 09:30:42,989 INFO sqlalchemy.engine.base.Engine {}
##########################
SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
{'owner': u'OWNER_NAME', 'table_name': u'DATABASE_VIEW_NAME'}
##########################
2015-02-02 09:30:43,032 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
2015-02-02 09:30:43,032 INFO sqlalchemy.engine.base.Engine {'owner': u'OWNER_NAME', 'table_name': u'DATABASE_VIEW_NAME'}
2015-02-02 09:30:43,375 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2015-02-02 09:30:43,375 INFO sqlalchemy.engine.base.Engine {'owner': u'OWNER_NAME', 'table_name': u'DATABASE_VIEW_NAME'}
2015-02-02 09:30:44,769 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name, b.uniqueness 
FROM ALL_IND_COLUMNS a, 
ALL_INDEXES b 
WHERE 
a.index_name = b.index_name 
AND a.table_owner = b.table_owner 
AND a.table_name = b.table_name 
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2015-02-02 09:30:44,770 INFO sqlalchemy.engine.base.Engine {'table_name': u'DATABASE_VIEW_NAME', 'schema': u'SCHEMA_NAME'}




################################
# instrocpect a database table
################################
from sqlalchemy import Table, create_engine, MetaData
engine = create_engine('oracle+cx_oracle://USER:PASSWORD@IP:PORT/DATABASE_NAME', 
                       echo=True # this enable log of all sql executed from a database engine
                      )
metadata = MetaData()
funds_table = Table('DATABASE_TABLE_NAME', metadata, autoload=True, autoload_with=engine, schema="SCHEMA_NAME")
##########################
SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
{'owner': u'OWNER_NAME', 'table_name': u'DATABASE_TABLE_NAME'}
##########################
2015-02-02 09:56:59,798 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
2015-02-02 09:56:59,798 INFO sqlalchemy.engine.base.Engine {'owner': u'OWNER_NAME', 'table_name': u'DATABASE_TABLE_NAME'}
2015-02-02 09:56:59,812 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2015-02-02 09:56:59,812 INFO sqlalchemy.engine.base.Engine {'owner': u'OWNER_NAME', 'table_name': u'DATABASE_TABLE_NAME'}
2015-02-02 09:57:02,388 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name, b.uniqueness 
FROM ALL_IND_COLUMNS a, 
ALL_INDEXES b 
WHERE 
a.index_name = b.index_name 
AND a.table_owner = b.table_owner 
AND a.table_name = b.table_name 
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2015-02-02 09:57:02,388 INFO sqlalchemy.engine.base.Engine {'table_name': u'DATABASE_TABLE_NAME', 'schema': u'SCHEMA_NAME'}

################################
# instrocpect a database synonym
################################
from sqlalchemy import Table, create_engine, MetaData
engine = create_engine('oracle+cx_oracle://USER:PASSWORD@IP:PORT/DATABASE_NAME', 
                      echo=True # this enable log of all sql executed from a database engine
                      )
metadata = MetaData()
funds_table = Table('DATABASE_TABLE_NAME', metadata, autoload=True, autoload_with=engine, schema="SCHEMA_NAME",
                    oracle_resolve_synonyms=True # this enable synonym-dblink-reflection, see: http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#synonym-dblink-reflection
                    )

2015-02-02 10:20:22,151 INFO sqlalchemy.engine.base.Engine SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name = :synonym_name AND owner = :desired_owner
2015-02-02 10:20:22,153 INFO sqlalchemy.engine.base.Engine {'desired_owner': u'OWNER_NAME', 'synonym_name': u'DATABASE_SYNONYM_NAME'}
##########################
SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
{'owner': 'ANOTHER_OWNER_NAME', 'table_name': 'DATABASE_SYNONYM_NAME'}
##########################
2015-02-02 10:20:22,980 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS WHERE table_name = :table_name AND owner = :owner  ORDER BY column_id
2015-02-02 10:20:22,980 INFO sqlalchemy.engine.base.Engine {'owner': 'ANOTHER_OWNER_NAME', 'table_name': 'DATABASE_SYNONYM_NAME'}
2015-02-02 10:20:23,045 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2015-02-02 10:20:23,045 INFO sqlalchemy.engine.base.Engine {'owner': 'ANOTHER_OWNER_NAME', 'table_name': 'DATABASE_SYNONYM_NAME'}
2015-02-02 10:20:24,292 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name, b.uniqueness 
FROM ALL_IND_COLUMNS a, 
ALL_INDEXES b 
WHERE 
a.index_name = b.index_name 
AND a.table_owner = b.table_owner 
AND a.table_name = b.table_name 
AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position
2015-02-02 10:20:24,292 INFO sqlalchemy.engine.base.Engine {'table_name': u'DATABASE_SYNONYM_NAME', 'schema': u'SCHEMA_NAME'}
2015-02-02 10:20:24,532 INFO sqlalchemy.engine.base.Engine SELECT
ac.constraint_name,
ac.constraint_type,
loc.column_name AS local_column,
rem.table_name AS remote_table,
rem.column_name AS remote_column,
rem.owner AS remote_owner,
loc.position as loc_pos,
rem.position as rem_pos
FROM all_constraints ac,
all_cons_columns loc,
all_cons_columns rem
WHERE ac.table_name = :table_name
AND ac.constraint_type IN ('R','P')
AND ac.owner = :owner
AND ac.owner = loc.owner
AND ac.constraint_name = loc.constraint_name
AND ac.r_owner = rem.owner(+)
AND ac.r_constraint_name = rem.constraint_name(+)
AND (rem.position IS NULL or loc.position=rem.position)
ORDER BY ac.constraint_name, loc.position
2015-02-02 10:20:24,533 INFO sqlalchemy.engine.base.Engine {'owner': u'OWNER_NAME', 'table_name': u'DATABASE_SYNONYM_NAME'}