Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, all while also supporting your previously written SQL*Plus scripts. Here is what is said about this tool by Oracle. As an…
Category: Database
How to update existing XML by changing or adding new elements in Oracle DB?
Assuming we have a task to update XML data stored in Oracle database table. Let it be XMLTYPE column. Actually it can either be CLOB or BLOB. Does not make big sense, because can go though conversions to XMLTYPE anyway. Just matter of performance. Here I’ll provide sample, how to proceed in 2 cases: Update XML by changing values in…
ORA-01555: snapshot too old: rollback segment number 91 with name “…” too small. What can be the cause?
Problem Get ORA-01555 exception during normal regular DB operations: ORA-01555: snapshot too old: rollback segment number 91 with name “_SYSSMU91_408937485$” too small Cause One of common causes could be that your UNDO tablespace is too small to serve for running DML operations. Whenever you start a DML operation, database has to save current state of related rowset for whatever time…
How to shrink Oracle database “undo” tablespace data file?
Sometimes it happens that we accidentally or unconsciously run very big DML transactions in database. Especially in ones, which dedicated for development purposes. As change history must be saved somewhere to enable a rollback of transaction, memory is necessary for that. And this is purpose which undo tablespace is serving for in Oracle database. And after some huge transactions it…
How to drop corrupted “undo” tablespace segment of Oracle database?
Once it happened that “undo” tablespace segments got corrupted for some reason, here you’ll find a way to get rid of them. Corrupted tablespace segment status is indicated as NEEDS RECOVERY. Check how many of them you have: Steps to drop corrupted segments: Gather all segment names together and write them into system parameter _OFFLINE_ROLLBACK_SEGMENTS. Here is a statement which generates…
How to edit or fix corrupted spfile of Oracle database ?
Once you’re reading this post, means your database is using SPFILE (Server Parameter File) to store parameters. It might be a case, that your DB is using PFILE (Parameter File) instead. You can check this by selecting value of parameter spfile: If you get path provided in value, means your database is using SPFILE to keep parameters. Otherwise its –…
Why can’t connect to Oracle instance using “sqlplus / as sysdba” ?
Problem Trying to connect to Oracle database instance directly from server command line. Once you have access to file system, you can connect to an instance without providing sys DB user credentials: After a try get this error message: ORA-12162: TNS:net service name is incorrectly specified Solution This may hapen because of not complete database server enviroment setup. In more…
How to enable and disable flashback for a table in Oracle database? How to create flashback data archive?
Here is just a reminder what is Oracle Flashback in general: Oracle Flashback Technology is a group of Oracle Database features that that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. Here are the steps which we need to accomplish if want to have table enabled…
What are possible message states in Oracle Advanced Queuing (AQ) ?
When we experience any problems with Oracle AQ (Advanced Queuing), messages are not going in or out, first place to look at is queue table itself. There is column STATE. It can tell us if a message is stuck for some reason or in any other state which can give us important information for debugging. Here are all states a message…
Is extremely big or low date, indicating infinity safe in SQL?
This is a question I’ll try to answer here. Quite frequently it happens to face SQL code, where extremum dates take place. From a first glance it look OK to use like to_date(‘3000.01.01′,’YYYY.MM.DD’)) for indicating infinity, something that lasts forever. Nobody could bet that no one of our developed nice IT system will even exist at such time point 🙂…