Something about Oracle

1. Steps to connect to Oracle server using SQL Developer after installing:

1. Navigate to Configuration and Migration Tools, choose Database Configuration Assistant.

2. Create a database (be sure to remember the Global DB name or SID)

3. Create a Net Service Name in Net Configuration Assistant (Host Name is your PC name) to allow accessing the database across the network.

4. Create a Schema as sysdba: run {Oracle_dbhome}/bin/sqlplus.exe then type in the following commands:

User: sys as sysdba
Password: {leave empty}
create user sample_schema IDENTIFIED BY oracle_pass;
grant dba to sample_schema;
grant connect to sample_schema; 

5. Open SQL Developer (client).

6. Create a connection with non-sysdba role.

2. Import from dmp file:

1. Open cmd.

2. Run "imp file=.dmp show=y" to view the user who exported the dump file (source user).

3. Execute the following command:

imp scott/tiger@example file=<file>.dmp fromuser=<source> touser=<dest>

5. Open SQL Developer (client).

6. Create a connection with non-sysdba role.

3. So many system tables?
When you log in with SYSDBA users, there will be very many system tables you see. This may make you chaotic. But this problem can be solved by creating a new user and then log in again with that new created user!

4. Operations with DATE datatype
For inserting, use TO_DATE function according with date format specified in second parameter:

insert into TAIKHOAN(NGAYDANGKY)
values (TO_DATE('2013/08/25 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

For selecting, updating or deleting, use TO_CHAR to cast date value to string:

select TO_CHAR(NGAYSINH, 'dd/mm/yyyy') 
from SINHVIEN;

You are doing everything right by using a to_date and to_char function and specifying the time format. The trouble is just that when you select a column of DATE datatype from the database, the default format mask doesn't show the time. If you issue a

alter session set nls_date_format = 'dd/MON/yyyy hh24:mi:ss'

you will see that the time successfully made it into the database!

5. How to display UTF8 string in Oracle client?
You may face problems in displaying the same Unicode string in your web application and in Oracle client software (such as SQL Developper).

4.1. For web application configuration, you just add following tag to your HTML content:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

4.2. For Oracle client software, you need to know about NLS_LANG environment parameter. It sets the language and territory used by the client application and the database server. It also indicates the client’s character set, which corresponds to the character set for data to be entered or displayed by a client program.

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:

NLS_LANG = language_territory.charset 

Now, let's access Run dialog, press regedit to do something.
(Vào Run, gõ regedit, sao đó trỏ đến node registry như đường dẫn sau rồi đặt giá trị cho nó)

+ From the Registry Editor, you point to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1. 
+ Double click on NLS_LANG node. 
+ Set the value to AMERICAN_AMERICA.AL32UTF8.

Next, please restart your machine so that our change can take effect.

Okay, until now, maybe I put a stop for my minitut here. Thank you for reading it!
(Nguyen My)