Shulou(Shulou.com)06/01 Report--

001mura-create a new table on an existing table

Create table t2

As select ename name,sal salary from emp

When T2 is born, there will be the data found in the subquery.

If you want to change the name of the column, use an alias.

If you do not want data, only establish the table structure, please add a false condition.

Create table T3 (C1, c2, c3) as

Select ename,empno,sal from emp where 9: 1

Attention, 002-drop table.

Instead of actually deleting the table, drop table has a name: look at the table statement of the Recycle Bin: SELECT * FROM USER_RECYCLEBIN

Restore the table of the Recycle Bin

FLASHBACK TABLE t2 TO BEFORE DROP

Change the name of the table while restoring it.

FLASHBACK TABLE T2 TO BEFORE DROP RENAME TO TT2

Empty the specified table in the Recycle Bin

PURGE TABLE T2

Clear the current user's Recycle Bin and will not affect other users' Recycle Bin

PURGE RECYCLEBIN

Bypass the Recycle Bin and delete the table thoroughly. There is no Recycle Bin before 10G, which means complete deletion. It is not easy to have a watch that is not available in the Recycle Bin.

Restored, I can only take a backup to restore.

Drop table t2 PURGE

Another way to delete truncate table table names cannot be reversed in this way

003-add comment

Create a table:

CREATE TABLE CORPREPORTERINFO (

CRID NUMBER NOT NULL

CORPID VARCHAR2 (8) NOT NULL

CRNAME VARCHAR2 (20)

PRIMARY KEY (CRID,CORPID)

)

TABLESPACE "AGR"

Add table comments:

COMMENT ON table CORPREPORTERINFO IS 'Reporter's form'

Add field comments:

Comment on column CORPREPORTERINFO.CRID is' id'

Comment on column CORPREPORTERINFO.CORPID is' Enterprise id'

Comment on column CORPREPORTERINFO.CRNAME is' name'

004-Database connection creation user

Command: CMD enters the dos window

Sqlplus / as sysdba

(1) create a user: CREATE USER username IDENTIFIED BY password

The code is as follows:

Create user user01 identified by u01

This command can also be used to set other permissions, as detailed in the self-study materials. To change a password, use the alter user command:

The copy code is as follows:

Alter user user01 identified by usr01

Now the password for user01 has been changed from "U01" to "usr01".

(2) to delete a user, you can use the drop user command, as follows:

Drop user user01

If the user owns the object, it cannot be deleted directly, otherwise an error value will be returned. Specify the keyword cascade to delete all objects owned by the user, and then delete the user. The following example is used to delete users and their objects:

Drop user user01 cascade

(3) authorization

To be compatible with previous versions, oracle provides three standard roles (role): connect, resource, and dba.

Grant connect, resource to user01

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.