During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.
Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…
Read the rest of this entry
On one database, I noticed that SQL*Plus did not show the heading of the columns when I ran a query. The result was this:
SQL> select instance_name,status from v$instance;
mydb OPEN
Even when entering “set heading on” didn’t change anything. I then investigated and found out that someone had changed the $ORACLE_HOME/sqlplus/admin/glogin.sql file (SQL*Plus runs this file on startup) and added the following lines:
SET LINESIZE 150
SET PAGESIZE 0
While this is quite nice and replaced the suboptimal default values, this was the cause for my problems mentioned above. Changing the line “SET PAGESIZE 0” to “SET PAGESIZE 1000” solved the problem and now, the query shows up the way I wanted:
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
mydb OPEN