Shulou(Shulou.com)05/31 Report--

This article will explain in detail what operation statements there are in the Oracle database, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

1. Oracle database operation

1. Create a database

Create database databasename

2. Delete the database

Drop database dbname

3. Back up the database

Full backup

Exp demo/demo@orcl buffer=1024 file=d:\ back.dmp full=y

Demo: user name, password

Buffer: cache siz

File: specific backup file address

Full: whether to export all files

Ignore: ignore the error, or overwrite if the table already exists

Export tables for system and sys users in the database

Exp demo/demo@orcl file=d:\ backup\ 1.dmp owner= (system,sys)

Export the specified table

Exp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

By filter criteria, export

Exp demo/demo@orcl file=d:\ back.dmp tables= (table1) query=\ "where filed1 like 'fg%'\"

You can compress the export; add compress=y after the command; if you need a log, then: log=d:\ log.txt

Back up the database of the remote server

Exp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

4. Database restore

Open cmd and execute the following command directly without having to log in to sqlplus.

Complete reduction

Imp demo/demo@orcl file=d:\ back.dmp full=y ignore=y log=D:\ implog.txt

It is important to specify the log so that errors can be analyzed and remedied.

Import the specified table

Imp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

Restore to a remote server

Imp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

II. Oracle table operation

1. Create a table

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],..)

Create a new table based on an existing table:

A:select * into table_new from table_old (create a new table using the old table)

B:create table tab_new as select col1,col2... From tab_old definition only

2. Delete the table

Drop table tabname

3. Rename the table

Description: alter table table name rename to new table name

Eg:alter table tablename rename to newtablename

4. Add a field

Description: alter table table name add (whether the default value of field name field type is empty)

Example: alter table tablename add (ID int)

Alter table tablename add (ID varchar2 (30) default 'empty' not null)

5. Modify the field

Description: alter table table name modify (whether the default value of field name field type is empty)

Eg:alter table tablename modify (ID number (4))

6. Duplicate name field

Description: alter table table name rename column column name to new column name (where: column is the keyword)

Eg:alter table tablename rename column ID to newID

7. Delete a field

Description: alter table table name drop column field name

Eg:alter table tablename drop column ID

8. Add primary key

Alter table tabname add primary key (col)

9. Delete the primary key

Alter table tabname drop primary key (col)

10. Create an index

Create [unique] index idxname on tabname (col … (.)

11. Delete the index

Drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

12. Create a view

Create view viewname as select statement

13. Delete view

Drop view viewname

3. Oracle operation data

1. Data query

Select from [where] [order by [asc or desc]]

2. Insert data

Insert into table name values (values for all columns)

Insert into test values (1 recorder zhangsanqie 20)

Insert into table name (column) values (corresponding value)

Insert into test (id,name) values (2)

3. Update data

Update table set column = new value [where condition]-- > Update records that meet the criteria

Update test set name='zhangsan2' where name='zhangsan'

Update table set column = new value-- > update all data

Update test set age = 20

4. Delete data

Delete from table name where condition-- > Delete records that meet the criteria

Delete from test where id = 1

Delete from test-- > Delete all

Commit;-- > submit data

Rollback;-- > rollback data

Deleted data can be recovered in delete mode, but there is no way to submit it. When delete deletes, logs will be recorded-- > deletion will be very slow.

Truncate table table name

Delete all data, will not affect the table structure, will not log, the data can not be recovered-- > delete quickly

Drop table table name

Delete all data, including the table structure, without logging, and the data cannot be recovered-- > delete quickly

5. Data replication

Table data replication

Insert into table1 (select * from table2)

Copy table structure

Create table table1 select * from table2 where 1 > 1

Copy table structure and data

Create table table1 select * from table2

Copy the specified field

Create table table1 as select id, name from table2 where 1 > 1

Database replication command

About which operation statements in the Oracle database are shared here, I hope the above content can be of some help to everyone and can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.