Oracle Database FAQ Summary (ii)

1.1. P l/sql Configuration

Tools -> Preferences->oracle-> Connections-> Check the connection

Plsql Configure the method for the four-bit Oracle :

http://blog.csdn.net/chen_zw/article/details/9292455/

1.2. query statement does not match Chinese problem

Execute the following query statement in the database to find out the current Chinese encoding

Select Userenv (' language ') from dual;

Simplified Chnese_china. Al32utf8

My Computer -> Properties-> Advanced system Settings-> Environment Variables-> System Variables- > New

nls= ' simplified Chnese_china. Al32utf8 '

after the increase is complete, restart PL/SQL can

1.3. database name, db instance name

To view the database name:

Show parameter db_name;

To view the DB instance name:

Show parameter instance_name;

To view the database listener name:

Cat/opt/oracle/oradb/home/network/admin/listener.ora

1.4. basic Information table in database dictionary

To view the details of each table space:

SELECT * from Dba_data_files;

SELECT * from Dba_tablespaces;

To view all user information:

SELECT * from Dba_users;

1.5. PL/SQL encoding Setup issues

Solve in PL/SQL, Chinese and date are garbled problems.

My Computer -> Properties-> Advanced system Settings-> Advanced-> Environment Variables

the environment variable the value of Nls_lang is set to:Chinese_china. ZHS16GBK or american_america. Al32utf8(do not mix the language with the code such as

Chinese_china. Al32utf8)

close and log back in PL/SQL client, making the settings effective.

1.6. Qlldr

When you need to import large amounts of data into the database, use the SQL Statement insertion efficiency is relatively low. Sqlldr is a good tool. You can import files in. txt,. csv, andExcel formats. Reference document " usage of SQL Loader . docx"

1.7. native Oracle login

SYSTEM/T_DPWDS8 Database:Oracle

1.8. Capitalization issues in SQL statements

General query statements are case-insensitive. However, the data dictionary is automatically converted to uppercase. So when querying the information in the data dictionary, you need to capitalize on the query.

SELECT * from user_indexes where LOWER (table_name) = ' student ';

1.9. Data Dictionary

The data dictionary is Oracle stores information about the database and uses it to describe the data.

select * from dictionary;

1.10. Data dictionary Common items

Query all table spaces:select * from Dba_tablespaces; SELECT * from All_tablespaces;

Query All users:select * from All_tables; SELECT * from Dba_tables;

query all sequences:select * from All_sequences; SELECT * from Dba_sequences;

query all triggers:select * from All_triggers; SELECT * from Dba_triggers;

1.11. View Database error interpretation information

Switch to Oracle User:oerr ora 02030

1.12. query The records of article 5 to article in the data sheet

SELECT * FROM (select T.*,t.rownum as num from table_a t) where num>=5 and num<=10;

1.13. querying the utilization of individual tablespaces

Select Df.tablespace_name "Table Space", Totalspace "total space M", round ((1-freespace/totalspace) *102,2) "Utilization" from (select Tablespace _name,round (sum (bytes)/1024/1024) from Dba_data_files Group by Tablespace_name) FS

1.14. single and double quotes in SQL

single quotes in There are three types of identities in Oracle:

(1) used to refer to a string constant, which defines the beginning and end of a string.

(2) escape character, escaping the characters (single quotation marks) immediately following the climate.

(3) More than hungry oh is it itself, that is, it appears in a string constant as part of a string.

the effect of double quotation marks is that if an object is created, the object name, the field name and double quotation marks are Oracle is case-sensitive, or Oracle defaults to uppercase.

When you alias a field name, use double quotes:select name "Name" from student;

Oracle Database FAQ Summary (ii)