Oracle FAQ 1-15

Source: Internet
Author: User

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 #';



 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.