Database

How to run Oracle SQLcl client directly as a Java class (skip sql.exe)?

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…

Continue Reading

Database

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…

Continue Reading

Database

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…

Continue Reading

Database

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…

Continue Reading

Database

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…

Continue Reading

Database

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…

Continue Reading

Database, SQL and PL/SQL

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 🙂…

Continue Reading