1. Why Learn Oracle?
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
Size of the project: A. How large the load is; B. cost; c. security
DBA Certification (OCA Certification-->OCP Certification--->OCM certification), Java Development Certification, Oracle Network Development certification.
Installation of 3.Oracle:
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
6. Introduction of Oracle Management tools:
How to use: Sql*plus in the Start menu, or SQLPLUSW in the run bar, or Sql*plus Worksheet in the Start menu;
To use: Enter Sqlplus in the run bar, or run the executable file Sqlplus.exe;
How to use: Start Menu OEM, independent landing;
7.sql*plus Common Commands
Eg: Show all commands Help index;
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.
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.
Description: This command is used to disconnect from the current database
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
Description: Displays the current user name
Description: This command disconnects the database and introduces
Save the SQL statement of the buffer to a file;
The SQL statements in the file are read into the buffer and run with/;
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;
Description: Edit the specified SQL script
Case: Sql>edit D:\a.sql
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
Note: You can override a variable, which, when executed, requires the user's input.
Case: Sql>select * from emp where job= ' &job ';
Description: def[ine] Variable name = variable Value
Description: Define--View all variables; Define variable name--view the value of the variable
Undef[ine] Variable name,...--clear one or more substitution variable values
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.
Description: Sets the width of the display line, which is 80 characters by default.
Case: Sql>show linesize; Sql>set Linesize 90;
Description: Sets the number of rows displayed per page, default to 14, and uses the same linesize.
8.Oracle User Management
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;
Eg: can be used directly: Sql>password user name;
Eg:sql>alter user username identified by new password;
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.
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 select on the EMP from Xiaoming;
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
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.
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;
sql>alter user xiaoming account unlock;
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;
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:
sql>create profile password_history limit password_life_time password_grace_time 2 Password_reuse_time 10;
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