Oracle FAQ 1-15

1. view the Oracle thread: PS-Ef | grep ora

 

2. What statements are used to query fields?

Select table_name from all_tables;
Desc table_name: You can query the table structure.

Select * From all_tab_columns where table_name = '?? '

 

3. How to query the number of tables in the database?
Select count (*) from all_tables;

 

4. How to display the current connected user

Show user;

 

5. How to execute a script SQL File

@ $ Path/filename. SQL;

 

6. What is the inverse function of CHR?
ASCII ()

Select CHR (65) from dual;

Select ASCII ('A') from dual;

 

7. Is the minimum integer greater than or equal to N returned?
Select Ceil (n) from dual;

Select Ceil (2008.1) from dual; // 2009

Select Ceil (-2008.1) from dual; //-2008

 

8. Is the minimum integer less than or equal to N returned?
Select floor (n) from dual;

Select floor (0, 2008.1) from dual; // 2008

Select floor (-2008.1) from dual; //-2009

 

9. How to test the time used for executing SQL statements?
Set timing on;
Select * From tablename;

10. How to export the select result to a text file?
Spool/home/Winnie/test.txt;
Select * From mytable;
Spool off;

 

11. rowid returns the physical address of the row
Select rowid from mytable;

Rowid

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

Aaactgaakaaabd8aaa

Aaactgaakaaabd8aab

 

12. currval and nextval create sequence for the table

Create sequence myseq start with 1 increment by 1;

Select myseq. currval from dual;

Insert into mytable values (myseq. nextval ,...);

Delete sequence: drop sequence myseq;

 

13. How do I find Repeated Records?

Select rowid, BM, MC from a where a. rowid! = (Select max (rowid) from a B where a. BM = B. bm and A. MC = B. MC );

 

14. Delete duplicate records
Delete from a A where a. rowid! = (Select max (rowid) from a B where a. BM = B. bm and A. MC = B. MC );

15. query Oracle locks

// Query Oracle locks

Select/* + rule */lpad ('', decode (L. xidusn, 0, 3, 0) | L. oracle_username user_name, O. owner, O. object_name, O. object_type, S. sid, S. serial #, S. machine from V $ locked_object L, dba_objects o, V $ session s where L. object_id = O. object_id and L. session_id = S. sid order by O. object_id, xidusn DESC

// Then kill the lock

Alter system kill session 'sid, serial #';