Summary of common Oracle statements

(Summary of the use of oracle9/10g, some SQL statements are practical)

------------- Step 1: Oracle Startup Process -----------
In the window:

Start a database instance
Oradim-startup-Sid orcl (database instance name );
Shut down database instances
Oradim-shuntdown-Sid orcl;
Start listening
Lnsrctl-start;
Disable listening
Lnsrctl-shuntdown;

In Linux:
LSNRCTL start -- start the listener
// Sqlplus sys/manager as sysdba -- this can be written in oracle10
Sqlplus/nolog
Conn sys/Oracle AS sysdba
Startup -- start a database instance

------ Go to Oracle and use system ---------
In general, in the window:
Enter sqlplus in the "Start" box to enter the sqlplus logon interface of oracle.

Sys/As sysdba // enter the username sys/password as the database administrator to go to Oracle.
Generally, sysdba can be used to successfully log on to the current window. However, the logon method is not safe enough.

System/Meiling // This login name is, use the system administrator identity to enter Oracle, password is Meiling (this password is in)
Remember what you set when installing oracle.

--------- User operation ------------------
SQL> create user test1 identified by test1; // create a user named test1 with the password test1
SQL> grant R1 to test1; // grant role R1 to user test1
SQL> grant unlimited tablespace to test1; // grant the table space usage permission to the test1 user, which cannot be granted to the role. Only for users.
SQL> drop user test1; // delete a user

Generally, the process of creating a user is as follows:
SQL> create user test identified by test; // create a user

The user has been created.

SQL> grant connect to test; // grant the logon role to the user

Authorization successful.

SQL> grant resource to test; // assign the role to the user to use the resource, that is, you can delete, modify, and create a sequence view of the table.

Authorization successful.

----------- Role operations
SQL> Create role R1; // creates an R1 role;
SQL> grant CREATE TABLE to R1; // grant the table creation permission to r1
SQL> grant create session to R1; // grant R1.
SQL> drop role R1; // delete a role;
SQL> revoke DBA from test; // revoke the DBA role of the test user
Pre-defined roles in the database:

1) connect, resource, and DBA are common permissions.

2) select_catalog_role: Permission for querying data dictionaries

3) delete_catalog_role: Permission for deleting data dictionaries

4) execute_catalog_role: Permission for executing the data dictionary package

--- Create data (this step is not required for programmers and is created by DBA)
Generally, there are three ways to create a database:
1. Use the GUI to create an instance, that is, create it in "Oracle Database Configuration Assisstant.
2. Create a database using the command line.
3. Create a database by specifying a batch file.

------------ Table operations (required for programmers to write books)
Create Table usertable (
Userid number (9) primary key,
Username varchar2 (64) not null,
Password varchar2 (128) not null,
Sex varchar2 (10 ),
Age number (3 ),
Address varchar2 (1000)
) // Create a usertable

SQL> ALTER TABLE usertable add phone varchar2 (32); // modify the table structure: Add a phone row to the table
SQL> ALTER TABLE usertable modify phone varchar2 (40); // modify the table structure: change the data type of phone to varchar2 (40)
SQL> ALTER TABLE usertable drop column phone; // modify the table structure: the phone field is deleted.
SQL> insert into usertable values (1, 'yxh', 'yxh', 'male', 26, '123'); // insert data
SQL> Update usertable set username = 'yuanxh' where userid = 1; // modify data
SQL> select * From usertable; // query data
SQL> Delete from usertable where userid = 1; // delete data
1 * insert into usertable values (user_sequence.nextval, 'yxh', 'yxh', 'mal', 26, 'ads
Fdsfa ') // insert a table using the sequence created as the primary key.
SQL> ALTER TABLE usertable Rename to users; // change the table name
SQL> comment on table users is 'user table'; // Add comments to the table
SQL> comment on column users. userid is 'user table id'; // Add a comment to the users table USERID field

----------- Sequence operations (required by programmers)
SQL> Create sequence user_sequence start with 1 increment by 1 maxvalue 1000000; // create a sequence

SQL> alter sequence user_sequence minvalue 0; // modify the sequence. However, start with cannot be modified.

SQL> select user_sequence.nextval from dual; // use SQL to obtain the next value of the sequence user_sequence

------------ View operations (required by programmers)

SQL> Create view user_view as select * From usertable; // create a view
SQL> Create or replace view user_view
2 select "userid", "username", "password", "sex", "Age" from usertable; // modify the View content
SQL> select * From user_view; // use View

--------- Use of stored procedures (required by programmers)
Create a stored procedure containing Input and Output Parameters
1 create or replace procedure user_proc (userid out number, username in varchar
2, password in varchar2)
2 is begin
3 insert into usertable (userid, username, password) values (user_sequence.nextva
L, username, password );
4 select user_sequence.currval into userid from dual;
5 * end user_proc;

Execute the Stored Procedure
In the command interface:
Exc user_proc ('',''); // It is only applicable when there are enough input parameters.
Execute user_proc ('',''); // It is only applicable when there are enough input parameters.
Call user_proc ('',''); // It is only applicable when there are enough input parameters.
Begin user_proc ('','') end; // this parameter is only applicable when an input parameter exists.

// Execution of parameters with input and output:
SQL> set serveroutput on
SQL> declare
2 spno number;
3 spname varchar2 (10 );
4 begin
5 sp_pro10 (spno, spname );
6
7 dbms_output.put_line (spname );
8 end;
9/

----- Import and Export of user objects, that is, using the exp and IMP commands is equivalent to backup and restore
Use of the export exp command
Data export:
1. Export the database test completely, and the username System Password Manager is exported to D: \ daochu. dmp.
Exp system/manager @ test file = D: \ daochu. dmp full = y
2. Export the tables of system users and SYS users in the database
Exp system/manager @ test file = D: \ daochu. dmp owner = (system, sys)
3. Export the inner_policy and policy_staff_relat tables in the database.
Exp aichannel/aichannel @ testdb2 file = D: \ datanewsmgnt. dmp tables = (inner_policy, policy_staff_relat)
4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ test file = D: \ daochu. dmp tables = (Table1) query = "where filed1 like '201312 '"

Export methods on the remote host:

C: \ Users \ zdvicloud> exp GDS/123@192.168.3.193/orcl file = E: \ data \ gddzjc. dmp tables
= (Om_organization)

Data Import
1. import data from D: \ daochu. dmp to the test database.
IMP system/manager @ test file = D: \ daochu. dmp
IMP aichannel/aichannel @ test full = y file = D: \ datanewsmgnt. dmp ignore = y
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = Y to the end.
2. Import table 1 in D: daochu. dmp
IMP system/manager @ test file = D: \ daochu. dmp tables = (Table1)