Few enhancement requests

[mxav1111]

Feature Requests

1. Can we search the given string in the entire database/schema and have it display schema_name.table_name.column_name and row number (rownum and rowid if oracle? ) containing the string?
2. crawler begins with some kind of constraint checks etc even though command is dump or quickdump., In case of dump commands, it just needs to dump whatever it can as soon as possible. Not sure exactly what is the need of performing those constraint etc pre-checks. This avoidance of checks can significantly boost performance. Or at least it needs to start dumping and hopefully not have any buffering (or if it is there, it would be great to have it as an option).
3. If there's an error like an privilege issue or something during crawl, can we have a optional flag to skip/ignore the errors and just keep going?

Thanks for the wonderful tool.

[sualeh]

@mxav1111 Thanks for the ideas.

1. You can search the entire database catalog using SchemaCrawler's powerful grep options. For example, you can use --grep-def to search the definitions. Please run SchemaCrawler with --help to see the options available.
2. What command-line options are you using? You can fine-tune the information level that can reduce some of these retrievals. The command-line option for this is --info-level. You can further fine tune by using the SchemaCrawler configuration file. I am sure that with tuning you can get SchemaCrawler to be very efficient for your use case.
3. SchemaCrawler already ignores many such exceptions and carries on. You will need to provide a specific example of what you are seeing. Use the consulting page to see how to provide detailed logs for debugging.

[mxav1111]

Thank you for your quick response.
As for first one, i couldn't find to search for data in the table. It is only allowing to search catalog (dictionary?) . I am looking for searching the data itself into the regular tables (non-catalog/non-dictionary tables). Or may be I am misunderstanding? If so, it would be kind of you if you could provide sample command line?

For second one, It is fairly large schema. However concern here is that it is taking real long time even before it could spit out first line of data. So size probably shouldn't matter to begin the dumping. Size would matter for time taken to extract total output but why for even to begin? I used quickdump and info-level was at lowest/false level. I need quickdump so that i can then grep using unix tools over it and i can quickly stop as soon as first string is found (using sed through pipe)

As for the last one, it wasn't able to ignore "no access to table". i.e. there were no privileges on that specific table.

Thanks again for helping out. Greatly appreciate it.

PS: I will get the command lines tomorrow to get better advise. Thanks again.

[sualeh]

@mxav1111

1. SchemaCrawler works with metadata, not with data. You can search for data using SchemaCrawler, but it will not be efficient and effective. You are better off using a SQL query to find data. If you would like to use a SQL query to find data across multiple schemas, you can consider using SchemaCrawler's scripting functionality using JavaScript or Python.
2. SchemaCrawler loads the metadata into memory before executing any of the commands. You can control how much metadata to load, given your use case. In a large database, it will take time to load the metadata, even though SchemaCrawler is multi-threaded. Please take a look at the fine-tuning options in the configuration file to control how much metadata is loaded.
3. The database user that you use with SchemaCrawler needs to have appropriate permissions to get metadata. Please send detailed SchemaCrawler logs.

[mxav1111]

For the first point, quickdump is able to extract ALL data too. (Please double check though). I was able to pass it to sed to print the matching line but it wasn't easy to print schema.table.column and row number and ids (in oracle) as that information is some lines above the string that was identified. Hence I requested for enhancement to avoid piping out etc.
So yes these were two main reasons for enhancement request. (a) quickdump to grep using external tool is not needed if there's inbuilt data grepping available (b) quickdump default performance is poor for large schemas. For this second point/performance concerns i will look into advance information of configuration file.

Thanks again for the great efforts and cool utility. Hopefully i can learn bit more and utilize it further.
Please feel free to close the request. Thank you.