Oracle FAQ

1. How to login to oracle without username and password?
from command prompt, run command 'sqlplus /nolog'

2. How to connect to sysdba?
SQL> connect sys/sys1 as sysdba

3. How to check init parameter values?
show parameter
ex: show parameter shared_pool_size
show parameter sga_max_size
show parameter shared_pool_size;

select value from v$parameter where name in ('background_dump_dest','user_dump_dest')

4. How to check SGA information?
select * from v$sgainfo;
SELECT * FROM v$sga;
SHOW SGA
select * from v$sga_dynamic_components;

5. How to monitor locks?

select * from v$session where username is not null and sid in ( select l.sid from v$lock l where l.block=1)

6. How to find control file location?
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files'
SELECT NAME FROM V$CONTROLFILE;

7. How to backup control files?
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

8. How to control archive log period?
ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 1800; – 30 minutes

9. How to make log switch?
ALTER SYSTEM SWITCH LOGFILE;

10. How to create tablespace?
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

11. How to start listener?
lsnrctl start svlist

12. How to find shared pool size?
select sum(bytes) from v$sgastat where pool='shared pool';

select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';

13. How to find current session schema?
select sys_context('USERENV', 'SESSION_SCHEMA') from dual;

14. How to change current session schema?
alter session set current_schema=VISTA;

15. How to find user objects count?
select owner, object_type,count(1) from dba_objects where status='INVALID' group by owner,object_type;

16. How to find invalid objects count?
select object_type,count(1) from user_objects where status='INVALID' group by object_order by 1;

17. How to compile user objects?
-- compile Schema level.
EXEC UTL_RECOMP.recomp_serial('MASTER');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- compile Database level.
EXEC SYS.UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- compile Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');

18. How to find locked objects?
SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

19. How to find disabled constraints?
select table_name,constraint_name,status from user_constraints where status='DISABLED';

20. How to prepare alter script to enable disabled constraints?

select 'alter table ' || table_name || ' enable constraint ' || constraint_name ||';' from user_constraints where status='DISABLED';

21. How to enable foreign key?
alter table table_name enable constraint constraint_name;

22. How to enable trigger?
ALTER TRIGGER trigger_name ENABLE;
ALTER TABLE table_name ENABLE ALL TRIGGERS;

23. How to prepare script to enable disabled triggers?
select 'alter trigger '|| trigger_name || ' enable;' from user_triggers where status='DISABLED'

24. How to find blocked sessions?
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

25. How to shrink datafile?
SELECT FILE_NAME,
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+);

26. How to find current session ID?
select sid from v$session where audsid = sys_context('userenv','sessionid');
select distinct sid from v$mystat;
select dbms_support.mysid from dual;

27. How to find scheduled jobs?
select * from dba_scheduler_jobs

28. How to oracle system date?
ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';

29. How to reset system date?
ALTER SYSTEM SET fixed_date = NONE;

30. How to drop database?
shutdown immediate;
startup mount restrict exclusive;
drop database;

31. How to display each table count?

CREATE OR REPLACE function VISTA.get_rows( p_tname in varchar2 ) return number
as
l_columnValue number default NULL;
begin
execute immediate
'select count(*)
from ' || p_tname INTO l_columnValue;

return l_columnValue;
end;
/

select owner, table_name,
get_rows( owner||'.'||table_name) cnt
from all_tables order by 3 desc;

32. How to check lock on a table?
select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial#,c.terminal, d.sql_text
from sys.dba_objects a,
v$locked_object b,
v$session c,
v$sqltext d
where a.object_id = b.object_id
and c.sid = b.session_id
and c.sql_hash_value = d.hash_value;

select c.sid,c.serial#,os_user_name from v$locked_object b, v$session c where c.sid = b.session_id;

33. How to kill a session?
alter system kill session 'c.sid, c.serial#'
ex:
alter system kill session '964, 9153';


34. How to find instance_name?
select instance_name from v$instance;