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
6. Know on which port oracle http and ftp server is running
a. Login from SYS
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
