Oracle Learning One: Installation and uninstallation, SQL *plus common commands, Oracle user management

1. Why Learn Oracle?

• Superior Performance:

Small database	Medium Database	Large database
Acess, Foxbase	MySQL, SQL Server, Informix	Sybase, Oracle, DB2
Small complexity (within 100 people), within the cost of thousands of dollars, the security requirements are not high	Daily visit Volume 5000--15000, cost in million, such as business site	Load can be processed, massive database, security is very high sybase<oracle<db2

• Criteria for selecting a database:

Size of the project: A. How large the load is; B. cost; c. security

• High demand:

2.Oracle Certification:

DBA Certification (OCA Certification-->OCP Certification--->OCM certification), Java Development Certification, Oracle Network Development certification.

Installation of 3.Oracle:

• The Oracle installation automatically generates SYS users and system users:

A. SYS user is superuser with the highest privileges, has the SYSDBA role, has the CREATE DATABASE permission, and the default password is Change_on_install.

B. The system user is a management operator, has a large authority, has a sysoper role, does not have the CREATE DATABASE permission, and the default password is manager.

C. In general, for database maintenance, use the system user to log on.

4. The difference between SQL Server and Oracle Startup:

After SQL Server----SQL Server starts, the user SA will see many databases after logging on.

After Oracle is started-----Oracle (start listening on a DB instance), user sys, system, and Scott see an instance of a database (data objects such as tables, views, stored functions, and so on). Oracle starts the service, starting at least one instance of Oracle and listening Tnlistener.

5. Uninstallation of Oracle

• Run regedit and enter the registration form;
• Enter hkey_local_machine\software\oracle, delete ORACLE;
• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services The key value starting with Oracle;
• Restart the computer, and then delete the Oracle directory on the hard disk, if the directory is not deleted, change the directory to a different name, restart the machine, and then delete it.

6. Introduction of Oracle Management tools:

• 1.Oracle of the tool software, mainly for the execution of SQL statements and PL/n blocks;

How to use: Sql*plus in the Start menu, or SQLPLUSW in the run bar, or Sql*plus Worksheet in the Start menu;

• 2.sqlplus DOS operation Oracle Tool, its function and sql*plus similar;

To use: Enter Sqlplus in the run bar, or run the executable file Sqlplus.exe;

• 3.oracle Enterprise Manager (Oracle Enterprise Managers);

How to use: Start Menu OEM, independent landing;

• 4.pl/sql developer belongs to third-party software and is mainly used to develop, test, and optimize Oracle PL/SQL stored procedures;

7.sql*plus Common Commands

• Help commands
• Help command or? Command

Eg: Show all commands Help index;

• Desc[ribe] Command
• Command format: Describe data object;

Returns a description of the objects stored in the database.

Tips: During the input of the SQL statement, you can enter # on the next line: The function is to run a SQL command temporarily, after execution, continue to enter the SQL statement just now.

• Connection command
• Conn[ect]

USAGE: Conn User name/password @ NETWORK SERVICE name [as Sysdba/sysoper], when connected as a privileged user, must be brought with as SYSDBA or as Sysoper.

• Disc[onnect]

Description: This command is used to disconnect from the current database

• Passw[ord]

Note: This command is used to modify the user's password, if you want to modify the other user's password, you need to login with Sys/system

• Show user

Description: Displays the current user name

• Exit/quit

Description: This command disconnects the database and introduces

• File Operations Command
• Sav[e] File Save path:

Save the SQL statement of the buffer to a file;

• Get file Read path

The SQL statements in the file are read into the buffer and run with/;

• Start and @ files [parameter 1, ...]

Description: Running SQL script--start can only run in Sqlplus, @ can run at Sqlplus and operating system command prompt

Case: No reference : sql>@ d:\a.sql or Sql>start d:\a.sql

reference : A.sql in the statement--select * from EMP where deptno=&1 and sal>&2;

Operation: @/start d:\a.sql 20 1500;

• Edit

Description: Edit the specified SQL script

Case: Sql>edit D:\a.sql

• Spool

Note: This command can output the contents of the Sql*plus screen to the specified file.

Case: Sql>spool d:\b.sql and input sql>spool off

• Interactive commands
• Using substitution variables--&

Note: You can override a variable, which, when executed, requires the user's input.

Case: Sql>select * from emp where job= ' &job ';

• To define substitution variables:

Description: def[ine] Variable name = variable Value

• To view the replacement variables:

Description: Define--View all variables; Define variable name--view the value of the variable

• Clearing substitution variables

Undef[ine] Variable name,...--clear one or more substitution variable values

• Displaying and setting environment variables

Overview: Various formats that can be used to control the output, set, show if you want to save the relevant settings permanently, you can modify the Glogin.sql script.

• Linesize:

Description: Sets the width of the display line, which is 80 characters by default.

Case: Sql>show linesize; Sql>set Linesize 90;

• PageSize

Description: Sets the number of rows displayed per page, default to 14, and uses the same linesize.

8.Oracle User Management

• Create user

Overview: To create a new user in Oracle, use the CREATE USER statement, typically with a DBA role.

Eg:create user username identified by password;

• Change Password for user
• If you change the password for yourself,

Eg: can be used directly: Sql>password user name;

• If you change the password for someone else, you need to have DBA authority, or have alter user's system permissions:

Eg:sql>alter user username identified by new password;

• Delete User

Overview: Generally as a DBA to delete a user, if you use other users to delete users, you need to have drop user permissions.

Eg:drop user username [cascade]----When a user is deleted, if there is a table created by that user, the user and user-created tables, that is, cascading deletes , are deleted.

• Grant and recycle of permissions
• Basic knowledge and association of permissions, roles, usernames, etc. in Oracle
  

• Detailed Explanation:

1. Each table in the Oracle database is in one user space (that is, a scenario), and the table names in each scenario can be the same; In addition: Each table is stored in a table space (equivalent to a disk partition).

2. The newly created user does not have any permissions (cannot connect to the database, not to query, etc.); only permissions/roles can be assigned to database operations;

3. Only DBAs, Oper, or users with privileges granted to the system can authorize system permissions:

Grant Connection Database permissions: Grant connect to new user;

Grant permissions to table operations in your own space: Grant resource to new users;

DBAs can also grant DBA authority: Grant DBA to New user;

4. When a user operates a table for another user, other users should authorize object permissions (Select/insert/update/all ...) to the user.

Scott grants the SELECT permission for the EMP table to the Xiaoming:grant select on the EMP to xiaoming;

5. When users query other users ' tables:

SELECT * from Scott.emp;

• Revoke permissions:

Revoke select on the EMP from Xiaoming;

• Database maintenance:

1. The Scott user gives other users permission to query Scott's EMP table and can continue to assign this permission to others.

---If object permissions, join with GRANT option

Eg:grant Select on EMP to xiaoming with GRANT option;

---If the system has permissions to Xiaoming, add the WITH admin option:

Eg:grant connect to xiaoming with admin option;

2. When you reclaim permissions, if Scott reclaims Xiaoming's query permissions on the EMP table, Xiaoming's permissions are also recycled

• Manage user slogans with profile

Overview: Profile is a collection of command for the slogan limit/resource limit, and when the database is established, Oracle automatically establishes a profile called default. When the setup user does not specify the profile option, Oracle assigns default to the user.

• Account lockout:

Overview: Specify the number of times that the account (user) can enter a password at logon, or specify the time the user is locked out (days), which is typically used as a DBA to execute the command.

Eg: Specify Xiaoming This user tries to log in up to 3 times, with a lockout time of 2 days:

sql>create profile lock_account limit failed_login_attempts 3password_lock_time 2;

sql>alter user xiaoming profile Lock_account;

• To unlock an account (user):

sql>alter user xiaoming account unlock;

• Terminate password:

Summary: In order for the user to periodically change the password can be done using the command to terminate the password, the same command also requires DBA identity to operate;

Eg: Create a profile for user xiaoming, requiring the user to modify their login password every 10 days with a grace period of two days.

sql>create profile myprofile limit password_lige_time password_grace_time 2;

sql>alter user xiaoming profile Myprofile;

• Password history

Overview: If you want users to be able to change passwords without using previously used passwords, the password history is used so that Oracle will store the performance of the password history in the data dictionary so that when the user modifies the password, Oracle compares the old and new passwords, and when the old and new passwords are found, The user is prompted to reenter the password. eg:

• Establish profile

sql>create profile password_history limit password_life_time password_grace_time 2 Password_reuse_time 10;

• Assign to a user (as before):
• Delete profile

Overview: When you don't need a profile file, you can delete it to a file.

sql>drop Profile password_history [Cascade (cascade Delete)];

Oracle Learning One: Installation and uninstallation, SQL *plus common commands, Oracle user management