Tag Archives: Oracle

How to empty an Oracle 11g XE database

Once I needed to empty an Oracle database that was used by several systems, so it was a big mess with tables not needed and a lot of trash. Anyway, here’s a script that will generate a file with drop commands, this is the one that will drop all objects in the database. The good thing […]

How to connect to a remote Oracle database

Here is how to connect to a remote Oracle database using SQLPlus: sqlplus user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=protocol)(HOST=host)(PORT=port))(CONNECT_DATA=(SID=sid))) Please note that the words in Italics represent variables: user – user name for the remote database pass – password for the remote database protocol – protocol used to connect to remote database, usually TCP host – IP address or hostname of the […]

How to solve ORA-25188: Cannot drop/disable/defer the primary key constraint for index-organized tables or sorted hash cluster

This time I was trying to reproduce an specific scenario and all of a sudden I was getting ORA-25188 in Tomcat’s console. A quick search on google revealed this method:  Connect as sysdba C:\Users\zulu>sqlplus / as sysdba Run the command, wait for the confirmation message SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. That’s it, at least […]

How to solve ORA-01017: Invalid username/password; logon denied

I was trying to connect to a database today when I found this: I think there could be a lot of reasons for this, but the easiest approach is to reset the user’s password, that is if the user exists of course. Here is the solution: Connect as sysdba to the database C:\Users\hecperez>sqlplus / as […]

How to check Oracle database version?

So today I needed to check what was my Oracle version because the PARTITION is not working and as far as I understand my version is not compatible with it. Here’s how to do it. Log in as sysdba C:\Users\recon1>sqlplus / as sysdba Run the following query SQL> select * from v$version; BANNER ——————————————————————————– Oracle […]

How to solve ORA-28001: The password has expired

https://www.techengineer.one/how-to-solve-ora-28001-the-password-has-expired/   The other day I was happily opening SQL Developer when I found this horrible thing. Here is how to solve it. Connect as sysdba to the database. C:\Users\Siry>sqlplus / as sysdba Run the query to set the password’s life time to unlimited. SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; Profile altered. Set a […]

How to execute several .SQL files from console

Yesterday I needed to run an older version of a project just to make a few changes, for that I needed to update my old code for that project and run all the scripts (.SQL files) to update the database. A colleague gave me a line of code to get the task done faster than executing each […]

How to execute .SQL files from console

Very easy, just log in into sqlplus and type as follows if you are in the same path as your file: SQL > @file_name.sql If you are not in the same path as your file, you should type as follows: SQL > @/path/to/file_name.sql What if I need to pass a parameter? Well, not a problem, […]

How to create Oracle users in Oracle 11g XE

Here is how it’s done. Log on as SYSTEM user C:\Users\delta1>sqlplus system SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 20 12:01:00 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production SQL> Execute the CREATE USER command SQL> create user user_name identified by […]