Oracle: Resolve “ORA-01704: string literal too long” and/or Non-ANSI Language Character Display Issues

You may experience issues when it comes to special characters. It could be DML (Insert/Update/Delete) where the data is not stored properly (or) display related (SELECT) where the characters do not display correctly as they are stored in the database. You could resolve both. Please continue reading on how to. Straight-forward case: The error "ORA-01704: …

Oracle: Clone An User With PL/SQL – Tablespace Quotas, Role Grants/Default Role, Object Grants And System Grants

As an Oracle DBA, you are very likely to get requests somewhat like this: Create an account for Sam Hill. The privileges should look exactly like Rob Nixon. If you are not into automating everything, your first instinct would be to fire-up an IDE like Toad or SQL Developer and script out the source user, …

Oracle – Sync Any Sequence And Any Table Column In A Generic Fashion To Avoid ORA-00001

Table to Sequence relation Typically, you have tables that rely on a sequence for their PK. Almost all of the time the relationship between a table and its sequence is one-to-one. Often times, as releases are done with manual data inserts, not too much attention is paid to use the value from the sequence and …

Oracle – Explain Plan Basics & Brief History

Whenever a SQL statement is submitted to the database server (be it Oracle or SQL Server), the server generates an execution plan which it uses to get the data in a well-defined order. The engine that does this work is called the “optimizer”. History of optimizers: RBO – Rule based optimizer Back in the day, …

Oracle – Quick Tip – Run OS Commands Within SQL*Plus

There are occasions when you might have the need to run Operating System commands inside SQL*Plus either as part of a script or as an individual command. If you have something simple to run as part of your .sql file that makes your script, just run them as follows: Here is an example that ran …