From 319c49e64ac52ef559d7a3f630de9291bb89e1dc Mon Sep 17 00:00:00 2001 From: David Fraser Date: Wed, 14 Sep 2016 15:10:42 -0400 Subject: Add exclude_tablespaces argument to Oracle Allows the SYSTEM and SYSAUX tablespaces to be only conditionally omitted when doing get_table_names() and get_temp_table_names(). Change-Id: Ie6995873f05163f2ce473a6a9c2d958a30681b44 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/292 --- lib/sqlalchemy/dialects/oracle/base.py | 59 +++++++++++++++++++++++++++------- 1 file changed, 47 insertions(+), 12 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 609a1da56..87e100410 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -195,6 +195,25 @@ accepted, including methods such as :meth:`.MetaData.reflect` and If synonyms are not in use, this flag should be left disabled. +Table names with SYSTEM/SYSAUX tablespaces +------------------------------------------- + +The :meth:`.Inspector.get_table_names` and +:meth:`.Inspector.get_temp_table_names` +methods each return a list of table names for the current engine. These methods +are also part of the reflection which occurs within an operation such as +:meth:`.MetaData.reflect`. By default, these operations exclude the ``SYSTEM`` +and ``SYSAUX`` tablespaces from the operation. In order to change this, the +default list of tablespaces excluded can be changed at the engine level using +the ``exclude_tablespaces`` parameter:: + + # exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM + e = create_engine( + "oracle://scott:tiger@xe", + exclude_tablespaces=["SYSAUX", "SOME_TABLESPACE"]) + +.. versionadded:: 1.1 + DateTime Compatibility ---------------------- @@ -979,11 +998,13 @@ class OracleDialect(default.DefaultDialect): use_ansi=True, optimize_limits=False, use_binds_for_limits=True, + exclude_tablespaces=('SYSTEM', 'SYSAUX', ), **kwargs): default.DefaultDialect.__init__(self, **kwargs) self.use_ansi = use_ansi self.optimize_limits = optimize_limits self.use_binds_for_limits = use_binds_for_limits + self.exclude_tablespaces = exclude_tablespaces def initialize(self, connection): super(OracleDialect, self).initialize(connection) @@ -1166,27 +1187,41 @@ class OracleDialect(default.DefaultDialect): # note that table_names() isn't loading DBLINKed or synonym'ed tables if schema is None: schema = self.default_schema_name - s = sql.text( - "SELECT table_name FROM all_tables " - "WHERE nvl(tablespace_name, 'no tablespace') NOT IN " - "('SYSTEM', 'SYSAUX') " - "AND OWNER = :owner " + + sql_str = "SELECT table_name FROM all_tables WHERE " + if self.exclude_tablespaces: + sql_str += ( + "nvl(tablespace_name, 'no tablespace') " + "NOT IN (%s) AND " % ( + ', '.join(["'%s'" % ts for ts in self.exclude_tablespaces]) + ) + ) + sql_str += ( + "OWNER = :owner " "AND IOT_NAME IS NULL " "AND DURATION IS NULL") - cursor = connection.execute(s, owner=schema) + + cursor = connection.execute(sql.text(sql_str), owner=schema) return [self.normalize_name(row[0]) for row in cursor] @reflection.cache def get_temp_table_names(self, connection, **kw): schema = self.denormalize_name(self.default_schema_name) - s = sql.text( - "SELECT table_name FROM all_tables " - "WHERE nvl(tablespace_name, 'no tablespace') NOT IN " - "('SYSTEM', 'SYSAUX') " - "AND OWNER = :owner " + + sql_str = "SELECT table_name FROM all_tables WHERE " + if self.exclude_tablespaces: + sql_str += ( + "nvl(tablespace_name, 'no tablespace') " + "NOT IN (%s) AND " % ( + ', '.join(["'%s'" % ts for ts in self.exclude_tablespaces]) + ) + ) + sql_str += ( + "OWNER = :owner " "AND IOT_NAME IS NULL " "AND DURATION IS NOT NULL") - cursor = connection.execute(s, owner=schema) + + cursor = connection.execute(sql.text(sql_str), owner=schema) return [self.normalize_name(row[0]) for row in cursor] @reflection.cache -- cgit v1.2.1