Some tips for everyday Oracle users

Setting the linesize and pagesize

set linesize 120;


Set the pagesize up to header

set pagesize n;

Few word aboud the user and constraints

desc user_cons_columns;
desc user_constraints;
desc user_sequences;

What is the table sctructure:

Describe tablename;
Desc tablename;

Check if the table is there

SELECT * from ALL_TABLES WHERE table_name = 'MyTable';


Show me all tables in the database server:

Select * from cat; tables and sequences

This is the hook:

SELECT TABLE_NAME FROM USER_TABLES;

Add rows

ALTER TABLE customer ADD (credit number(7,0));

Remove rows
ALTER TABLE customer DROP (credit);

Change attribute, only if all values in the row are NULL

ALTER TABLE customer MODIFY (credit number(12,0));

Show me constraints for a specific table

SELECT constraint_name, constraint_type FROM user_constraints
WHERE table_name='CUSTOMER';

And for which row are this constraints in the table ?

SELECT constraint_name, column_name FROM user_cons_columns
WHERE table_name='CUSTOMER';

? Yes, the table name must be written UPPER CASE

Constraints activate/deactivate

ALTER TABLE customer MODIFY CONSTRAINT max ENABLE/DISABLE;

Constraint status

select status FROM user_constraints WHERE table_name='CUSTOMER';

Show me the objecttypes, that belongs to a specific user

SELECT DISTINCT object_type FROM user_objects;


Select all tables, synonyme, views, sequences


select * from user_catalog;


Show sequences :


select * from user_sequences;


Show trigger:


Select * from user_triggers;
Select * from user_triggers where trigger_name = ?TRIGGER_CUSTOMER?;
select status from user_triggers where trigger_name = ?TRIGGER_CUSTOMER?;
Select description from user_triggers where trigger_name = ?TRIGGER_CUSTOMER?;


Show constraints for a table:

SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'CUSTOMER?;
SELECT constraint_name, constraint_type, search_condition FROM user_constraints WHERE table_name = 'CUSTOMER';


Constraints spalte:

SELECT constraint_name, column_name FROM user_cons_columns WHERE table_name = 'HANGAR';

Delete all data from a table


DELETE FROM table_name;