I'm running SchemaCrawler command line against Oracle and I can't get the Oracle Sequences in the output. I only get the tables that match my regex. I've confirmed that the sequences exist in the schema and that the user has access to them. What am I doing wrong? My configuration is below.
SchemaCrawler: 12.01.01
Oracle: 11g Enterprise Edition Release 11.2.0.4.0
Driver: Oracle JDBC driver 11.1.0.7.0-Production
Command Line: sc.cmd -host="my host" -user="my user" -password="my password" -server=oracle -url=jdbc:oracle:thin:@myHost:1521/myService -database="myService" -driver=oracle.jdbc.OracleDriver -schemas="mySchema" -routines= -c=schema -infolevel=standard -loglevel=FINE
Properties:
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# ------------------------------- SchemaCrawler --------------------------------
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# SchemaCrawler is a platform (both operating system and database system)
# independent command-line tool to output your database schema and data in a
# readable form. The output is designed to be diff-ed with previous versions of
# your database schema.
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# SchemaCrawler Options
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Filtering Options
# ------------------------------------------------------------------------------
# Regular expression table and column name pattern to filter table
# and column names
# Column regular expression to match fully qualified column names,
# in the form "SCHEMANAME.TABLENAME.COLUMNNAME"
# Default: .* for include, <none> for exclude
schemacrawler.table.pattern.include=.*\.PERSOL.*
schemacrawler.table.pattern.exclude=
schemacrawler.column.pattern.include=.*
schemacrawler.column.pattern.exclude=
# Regular expression procedure and procedure parameter name pattern to filter
# procedure and procedure parameter names
# Default: .* for include, <none> for exclude
schemacrawler.procedure.pattern.include=
schemacrawler.procedure.pattern.exclude=.*
schemacrawler.procedure.inout.pattern.include=
schemacrawler.procedure.inout.pattern.exclude=.*
# Regular expression synonym pattern to filter
# synonym names
# Default: <none> for include, .* for exclude
schemacrawler.synonym.pattern.include=.*\.PERSOL.*
schemacrawler.synonym.pattern.exclude=
# Regular expression sequence pattern to filter
# sequence names
# Default: <none> for include, .* for exclude
schemacrawler.sequence.pattern.include=.*
schemacrawler.sequence.pattern.exclude=
# Grep Options
# ------------------------------------------------------------------------------
schemacrawler.grep.column.pattern.exclude=
schemacrawler.grep.column.pattern.include=.*
schemacrawler.grep.procedure.inout.pattern.exclude=
schemacrawler.grep.procedure.inout.pattern.include=.*
schemacrawler.grep.invert-match=false
# Sorting Options
# ------------------------------------------------------------------------------
# Sort orders for objects that are not sorted by name by default
schemacrawler.format.sort_alphabetically.table_columns=false
schemacrawler.format.sort_alphabetically.table_foreignkeys=false
schemacrawler.format.sort_alphabetically.table_indices=false
schemacrawler.format.sort_alphabetically.procedure_columns=false
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Text Formatting Options
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Shows all object names with the catalog and schema names, for easier comparison
# across different schemas
# Default: false
schemacrawler.format.show_unqualified_names=false
# Shows standard column names instead of database specific column names
# Default: false
schemacrawler.format.show_standard_column_type_names=false
# Shows ordinal numbers for columns
# Default: false
schemacrawler.format.show_ordinal_numbers=false
# If foreign key names, constraint names, trigger names,
# specific names for procedures, or index and primary key names
# are not explicitly provided while creating a schema, most
# database systems assign default names. These names can show
# up as spurious diffs in SchemaCrawler output.
#
# All of these are hidden with the -portablenames
# command-line option.
#
# Hides foreign key names, constraint names, trigger names,
# specific names for procedures, index and primary key names
# Default: false
schemacrawler.format.hide_primarykey_names=false
schemacrawler.format.hide_foreignkey_names=false
schemacrawler.format.hide_index_names=false
schemacrawler.format.hide_trigger_names=false
schemacrawler.format.hide_routine_specific_names=false
schemacrawler.format.hide_constraint_names=false
# Encoding of input files, such as Apache Velocity temaplates
# Default: UTF-8
schemacrawler.encoding.input=UTF-8
# Encoding of SchemaCrawler output files
# Default: UTF-8
schemacrawler.encoding.output=UTF-8
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Data Output Options
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Whether to merge rows that differ only in the last column
# Default: false
schemacrawler.data.merge_rows=false
# Whether to show data from CLOB and BLOB objects
# Default: false
schemacrawler.data.show_lobs=false
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
# Queries
# --=----=----=----=----=----=----=----=----=----=----=----=----=----=----=----=
Edit
I can run the SQL below using the account I'm connecting with and see the sequences I'm interested in.
SELECT /*+ PARALLEL(AUTO) */
NULL AS SEQUENCE_CATALOG,
SEQUENCE_OWNER AS SEQUENCE_SCHEMA,
SEQUENCE_NAME AS SEQUENCE_NAME,
INCREMENT_BY AS "INCREMENT",
MIN_VALUE AS MINIMUM_VALUE,
MAX_VALUE AS MAXIMUM_VALUE,
CASE WHEN CYCLE_FLAG = 'Y' THEN 'YES' ELSE 'NO' END AS CYCLE_OPTION,
ORDER_FLAG,
CACHE_SIZE,
LAST_NUMBER
FROM
ALL_SEQUENCES
WHERE
SEQUENCE_OWNER NOT IN ('CTXSYS', 'DBSNMP', 'DMSYS', 'MDDATA', 'MDSYS', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'XDB')
AND SEQUENCE_OWNER NOT LIKE 'APEX%' and SEQUENCE_OWNER = 'MONET' AND SEQUENCE_NAME LIKE '%PERSOL%'
ORDER BY
SEQUENCE_OWNER,
SEQUENCE_NAME
SchemaCrawler offer two ways to connect to a database. One way is to provide a JDBC driver, url combination, and another is to provide a server, host, and port combination. The second combination (server, host, and port) will give you rich database specific data, more than what JDBC provides out of the box, such as Oracle sequence information. (In your command-line above, you seem to have mixed these two modes of connection, and SchemaCrawler has picked the generic way to connect to Oracle.)
Please try the following modified command-line, and see if that works for you:
sc.cmd -user="my user" -password="my password" -server=oracle -host=myHost -database="myService" -schemas="mySchema" -routines= -sequences=.* -c=schema -infolevel=maximumEDITS
Sualeh Fatehi, SchemaCrawler