Usefull oracle commands

1) sqlplus login
----------------
sqlplus user_name/password@SID


2) Executing a sql scrip in the sql prompt
------------------------------------------
sql>@insertscriptssql.sql


3)Get the last executed statement from sql promt
------------------------------------------------
Execute last statement = /


4)Set the sql view for more human readable format
--------------------------------------------------
SQL> set linesize 300
SQL> set pagesize 500


5) Selecting indexes
---------------------
select index_name,UNIQUENESS,INDEX_TYPE from user_indexes where table_name='TABLE_NAME';
select index_name, column_name from user_ind_columns where table_name = 'TABLE_NAME';



6) Creating a index
--------------------
CREATE INDEX idx_accountholder_msisdn ON ACCOUNTHOLDER (MSISDN) COMPUTE STATISTICS;
CREATE INDEX IDX_SIMCOUNTER_MSISDN ON DEALER_SIM_COUNTER(MSISDN, ICCID);


7) Selecting the ACTIVE/INACTIVE sessions
------------------------------------------
log as system user-> ROOT access = system/password
SELECT USERNAME, STATUS FROM V$SESSION WHERE USERNAME LIKE '%User_name%';

8) Disabling the constraints
-----------------------------
alter table TABLE_NAME disable constraint CONSTRAINT_NAME;


9) Executing a sql script with a stored procedure
--------------------------------------------------
1. Edit the sql scrip with following at the end (carefull about the space line above the .(dot))

------------------

.

RUN;
-------------------
2. Execute with sql>@scrip_name.sql
3. Check the creation sql>desc STORED_PROCEDURE_NAME;


10) Get the Oracle version
--------------------------
select * from v$version where banner like '%Oracle%';