Oracle12c operation command

Oracle operation command 1. Create a user

You need to create a tablespace first

SQL > create tablespace work # create work tablespace

2 datafile'/ orc/app/oracle/oradata/work01.dbf' # define the path

3 size 100m autoextend on; # size is 100m

SQL > create user c##dba # create user as dba

2 identified by 123123 # password 123123

3 default tablespace work # default database work

4 temporary tablespace temp #

5 quota unlimited on work # No quota is set

6 password expire; # password needs to be changed after each login

2. Change the account password

SQL > alter user c##dba identified by abc123

3. Delete a user

SQL > drop user c##dba cascade

4. User login database authorization

Grant connect,resource to c##dba

5. Revoke user rights

SQL > revoke connect, resource from c##dba

6. Create tables

SQL > create table list

2 (

3 id number (4) constraint only_id primary key

4 name varchar2 (10)

5 score number (5par 2)

6 born date

7 address varchar2 (50)

8)

7. View the table structure

SQL > desc list

8. Transaction

SQL > insert into list values (1 SQL > beijing'); # insert data of zhangsan

SQL > insert into list values; # insert Li Si's data into the data of Li Si

SQL > commit; # submission completed

SQL > insert into list values; # add the data of wangwu again ('2018-10-9), tianjin')

SQL > rollback; # rollback

SQL > select * from list; # will check to see that wangwu data has not been submitted

9. Turn off and enable automatic submission

SQL > set autocommit on; # enable autocommit

SQL > insert into list values; # insert data, you will be prompted to complete the submission.

SQL > set autocommit off; # turn off autocommit

SQL > insert into list values; # insert data again

SQL > rollback; # prompt fallback is complete

10. Create an index

SQL > create index index_list on list (score); # create score generic index

SQL > create unique index id_unique on list (id); # create a unique index

SQL > create index reverse_source on list (score) reverse; # create reverse index

SQL > create bitmap index add_index on list (address); # create bitmap index

SQL > create index other_index on list (upper (id)); # create additional indexes (upper (id))

Select index_name,index_type,table_name,tablespace_name from user_indexes; # View Index

SQL > alter index other_index rebuild; # re-index

SQL > alter index other_index rebuild tablespace work; # re-index

SQL > alter index other_index coalesce; # merge index fragments

SQL > drop index other_index; # delete index

11. Create and view views

Sqlplus / as sysdba # advanced top administrator user

SQL > grant create any view to Centraldba; # raise rights to c##dba users

Then log in with the c##dba user

SQL > create view view_list as select from list where id=1; # create a view

SQL > select from view_list; # View View

12. Materialized view

First, the user c##dba created by us is authorized by the top administrator of sys

SQL > grant create materialized view to canaledba; # empower to create materialized views

SQL > grant query rewrite to canaledba; # empowered query and rewrite

SQL > grant create any table to canaledba; # authorizes the creation of any table

SQL > grant select any table to canaledba; # entitles to view any table

Switch back to c##dba user

SQL > conn c##dba/123123

First create a materialized view log

SQL > create materialized view log on list with rowid

SQL > create materialized view mtrlview_list

2 build immediate

3 refresh fast

4 on commit

5 enable query rewrite

6 as

7 select * from list where id = '1clients; # create a materialized view with id 1 for list

Delete materialized view

SQL > drop materialized view mtrlview_list