Oracle11g allows you to create tablespaces, create users, authorize roles, Import and Export tables, and garbled Chinese characters.

Oracle11g allows you to create tablespaces, create users, authorize roles, Import and Export tables, and garbled Chinese characters.
Premise: the Oracle11g database has been installed on the local machine. Requirement: Use the PL/sqldatabase Connection Tool to operate Oracle database 1. To create a tablespace and operate a database, you must first create a user and grant permissions to the user; when creating a user, you must specify the tablespace and the size that the user can use in the tablespace. Therefore, to create a user, you must first create a database tablespace. 1. Use sqlplus to create users and tablespaces: (1) Open sqlplus: start -- all programs -- Oracle -- OraDb11g_home1 -- Application Development -- SQL Plus (2) create a tablespace: connect to the database using the user system. When the password is used to install the database for you, set the management password set in the windows of the global database. The initial password for SYSTEM, SYS, SYSMAN, and DBSNMP is the password, the initial password of SCOTT is tiger. Statement for creating a tablespace: datafile2.dbf indicates the newly created tablespace data file. Note: The Directory (D: \ OracleFiles \ OracleData) for storing the tablespace data file must already exist, otherwise, the following error will be reported, which is defined by the Directory, not necessarily the same as that in the middle. After the location of the data file is modified, the tablespace is created successfully. The local management method is used to create the tablespace. dbsp_2 is the tablespace name, and size 10 m indicates the tablespace size, extent management local autoallocate is used to expand the tablespace when the tablespace size is full. Another extension method is to expand the same size each time. The statement is extent management local uniform size 256 K. Among them, the K is set based on actual needs. Table space size query statement: (3) create user: The first testUser is the user name, the second testUser is the user password, and the default tablespace is the default table space, which is set to the table space created above, temporary tablespace is a temporary tablespace. quote sets the size of the space occupied by the user on the tablespace. If the default tablespace is not specified, Oracle specifies the SYSTEM tablespace as the user's default tablespace. If the quota clause is not used, the user's quota in the specified tablespace is 0, you cannot create data objects in the tablespace. After a user is created, when the space allocated by the user is insufficient, the user space can be extended: alter user testUser quota 20 m on dbsp_2; the user space can be extended to 20 m to change the user password: alter user testUser identified by 123456; set the password to 123456. Sometimes the user is locked to unlock the user: alter user testUser account unlock; (3) the user who sets the initial permission for the user does not have any permissions and cannot perform any database operations, therefore, you must set permissions or roles for the user. Users granted with a role will have the permissions assigned to this role. The system predefined roles that are commonly used are CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, and IMP_FULL_DATABASE. CONNECT, RESOURCE, and DBA are mainly used for database management. The database administrator must be assigned these three roles. Generally, database developers must be granted the CONNECT and RESOURCE roles. The EXP_FULL_DATABASE and IMP_FULL_DATABASE roles are used for database export and import operations respectively. Authorize a role to a user: (4) create a user and authorize the user. Use the pl SQL tool to double-click to run the PLSQL Developer Software and connect to the local oracle database server.

 

Under "object", find users, right-click and select "new"

 

In the pop-up "create user" window, enter the new user name, password, default tablespace, temporary tablespace, etc.

 

 

Grant permissions to new users and their role permissions: connect and resource so that users can log on to the database.

 

(5) After creating a database user, you can use the user name and password to log on to the pl SQL tool to operate the database. Ii. Import and export of database data: After pl SQL is successfully logged on, click tool> export table> select the table to be exported> insert SQL> Delete table, and remove the check mark that includes the storage, select the location of the output file, click Export, and wait for the export to complete.

 

Data Import: click "new" in the upper left corner of pl SQL. The command window is displayed.

 

Enter the @ + SQL file location and press enter to import the table to the database.

 

During database Import, the "unable to create INITIAL extent for segment in tablespace" error may occur when the report space is insufficient to modify the tablespace data file size: alter database datafile 'xxx. dbf'autoextend on next 10 m [maxsize 500 m]; each expansion is 10 m, and the maximum size is 500 m. If the subsequent tablespace is insufficient, you can modify the maxsize value. Iii. Oracle Database garbled characters: 1. During import and export, Chinese characters in the exported SQL file or pl SQL may be garbled and question marks may be displayed:

 

It may be because the encoding format of the ORACLE database does not support Chinese characters. The modification method is as follows: (1) Start-run-enter regedit-press enter to enter the registry, choose HKEY_LOCAL_MACHINE ---> SOFTWARE ---> ORACLE ---> KEY_OraDb11g_home1 (different versions of Oracle display different, but all contain the word "home") and find "NLS_LANG ", check whether the value is "SIMPLIFIED CHINESE_CHINA.ZHS16GBK". If not, set it to "SIMPLIFIED CHINESE_CHINA.ZHS16GBK."

 

(2) After the registry is set, set our environment variables, computer (right-click) ---> properties ---> advanced system settings ---> advanced ---> environment variables ---> new, we recommend that you create a new user variable. The variable name is "NLS_LANG" and the variable value is "SIMPLIFIED CHINESE_CHINA.ZHS16GBK ". Click OK. At this point, the settings are complete.

 

(3) After this setting, the SQL file and pl SQL should be able to display Chinese characters. 2. After the above steps, some tables may display Chinese characters and some tables still garbled characters. The prompt is displayed when you log on to the pl SQL tool: "The database character set is different from the client character set, and character conversion may cause unexpected results." reason: the character encoding formats of the ORACLE server and the client are different, that is, the encoding format in the modified registry is different from that in the Oracle database. Solution: Open the sqlplus window and view the current encoding format:

My database has already been configured. It is the same as the character set in the Registry above. If it is different, you need to change the character set as follows:

SQL> conn/as sysdba; SQL> shutdown immediate; database closed. database dismounted. oracle instance shut down. SQL> startup mount; oracle instance started. total system global area 135337420 bytes fixed size 452044 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes database mounted. SQL> alter system enable restricted session; system altered. SQL> alter System set job_queue_processes = 0; system altered. SQL> alter system set aq_tm_processes = 0; system altered. SQL> alter database open; database altered. SQL> alter database character set internal_use ZHS16GBK SQL> shutdown immediate; SQL> startup; after the above steps, the character set of the database server and the client is consistent, there should be no Chinese Character garbled characters. The above is a problem with database operations today. Now we should be able to use the local Oracle11g database normally. Profile: Graphical Method create user: http://jingyan.baidu.com/article/375c8e1997522e25f2a229c3.html modify Oracle registry and environment variable character set: http://www.2cto.com/database/201407/313317.html modify Oracle Character Set: http://blog.sina.com.cn/s/blog_4b055d6b0100sssg.html