Some common operations of Oracle ____oracle

I. Permissions-related

1. Landing

Sqlplus System/admin as SYSDBA; Administrator Login System Account

Sqlplus Scott/tiger; Log on to regular user Scott

2. Managing Users

Create user Wahaha; Under the Administrator account, create a user Zhangsan
Alert user Scott identified by Tiger; Modify password
Create user Zhangsan; Under the Administrator account, create a user Zhangsan
Alert user Scott identified by Tiger; Modify password

3. Granting of authority

/* Admin Authorization * *
Grant create session to Wahaha; Grant Zhangsan user the right to create a session, that is, login permissions, all have
Grant unlimited session to Wahaha; Grant Zhangsan users permission to use the table space
Grant CREATE table to Wahaha; Grant permission to create a table
Grante drop table to Wahaha; Grant permission to delete a table
Grant Insert table to Wahaha; Permission to insert a table
Grant Update table to Wahaha; To modify a table's permissions
Grant all to public; This is more important and grants all permissions (all) to all users (public)

/*oralce on the Authority management is more rigorous, common users are also default can not access each other * *
Grant SELECT on TableName to Wahaha; Grant Zhangsan users permission to view the specified table
Grant drop on TableName to Wahaha; Grant permission to delete a table
Grant insert on TableName to Wahaha; Grant inserted permissions
Grant update on TableName to Wahaha; Grant permission to modify a table
Grant Insert (ID) on tablename to Wahaha;
Grant Update (ID) on tablename to Wahaha; Grant insert and Modify permissions for specific fields in the specified table, note that only insert and update
Grant alert all table to Wahaha; Grant Zhangsan user alert any table permission

4. Revoke permission
Basic syntax with GRANT, keyword revoke

5. View Permissions

SELECT * from User_sys_privs; View all permissions for the current user
SELECT * from User_tab_privs; View permissions for a table used by the user
SELECT * from User_sys_privs; View all permissions for the current user
SELECT * from User_tab_privs; View permissions for a table used by the user

6. Grant User Administrator privileges
Create user Songuo identified by admin;
Grant create session to Songuo with admin option;
Gran umlimited tablespace to Songuo with admin option;
Grant DBA to Songuo with admin option;

Second, the table structure

1. Add Field

ALTER TABLE Products ADD description text;

You can also define constraints on this field at the same time:

ALTER TABLE Products ADD Description text CHECK (description <> ' 100 ');

2. Delete fields

ALTER TABLE Products DROP COLUMN description [CASCADE];

3. Increased constraints

To add a constraint, use the table constraint syntax, such as:
ALTER TABLE Products ADD CHECK (name <> ');
ALTER TABLE Products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE Products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE Teacher Add constraint df_sex default (' man ') for sex

To add a non-empty constraint that cannot be written as a table constraint, use the following syntax:
Alter TABLE products ' alter COLUMN product_no SET not NULL;
This constraint is checked immediately, so the table must conform to the constraint before adding the constraint.

4. Delete constraint

ALTER TABLE Products DROP CONSTRAINT some_name [CASCADE];

To delete a non-empty type:
Alter TABLE, products alter COLUMN product_no DROP not NULL;

5. Modify the data type of a field

ALTER Table name Modify (field name VARCHAR2 (20), Field name VARCHAR2 (50))
Alter TABLE products, alter COLUMN price TYPE numeric (10,2);

6. Change the name to the field
ALTER TABLE Products RENAME COLUMN product_no to Product_number;

8, to change the name of the table

ALTER TABLE products RENAME to items;