Oracle Exploration

1. Find index on table:

select * from all_indexes where table_owner='DB_NAME' and table_name='TABLE_NAME'

This will show what all indexes are there on the table with name 'TABLE_NAME'. It won't show what is the name of the field on which index exists.

2. Find the column name which are indexed:

SELECT *
FROM ALL_IND_COLUMNS
WHERE table_name = 'TABLE_NAME
ORDER BY index_name, column_name;

You will find some of the index_name are starting with SYS_, these are index on the primary keys which are generated while defining primary key on table. Others are created manually.

3. How to generate DDL for a view:

set pagesize 0
set long 90000

SELECT DBMS_METADATA.GET_DDL('VIEW','view_name') FROM dual;

4. Get only few rows from query

SELECT column FROM table
WHERE ROWNUM <= 10

5. Wondering why your query started taking time on table where you have started computation by using command

ANALYZE TABLE user_data COMPUTE STATISTICS;

Run the command 

ANALYZE TABLE user_data DELETE STATISTICS;

6. Know on which port oracle http and ftp server is running
a. Login from SYS

b. select dbms_xdb.gethttpport as "HTTP-Port", dbms_xdb.getftpport as "FTP-Port" from dual;

7. Change the port on which oracle http and ftp server is running

SQL> begin
2    dbms_xdb.sethttpport('0');
3    dbms_xdb.setftpport('0');
4  end;
5  /

For value 0 above function will disable the http and ftp services of oracle. If you want to run it on some other port then instead of port 0 write the desired port.

8. Export and import the database
Export database:
exp / file=<.dmp file> log=emp.log rows=yes indexes=yes
Import database
imp / file=<.dmp file> fromuser= touser=

9. How to check lock on tables

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,

b.object_id,substr(b.object_name,1,40) object_name

from v$session a, dba_objects b, v$locked_object c

where a.sid = c.session_id

and b.object_id = c.object_id

10. How to create index on CLOB

create index  on  () indextype IS ctxsys.context;

11. To know about the open connection in oracle

select username , count(1) from v$session group by username
Empty usename is connection opened by oracle