Oracle11g create, modify, and delete tables, and oracle11g modify and Delete tables

Oracle11g create, modify, and delete tables, and oracle11g modify and Delete tables

Oracle11g create, modify, and delete a table

My Database Name: ORCL password: 123456

 

1. Mode

2. Create a table

3. Table Constraints

4. modify a table

5. delete a table

1. Mode

Set oracle_sid = ORCL

Sqlplus/nolog

1) enter the same name mode (for the first time you use it, you may need to set it in the Appendix. I set the scott User Password 123)

Connect scott/123

Show user

2) Enter sys mode (sys Columns cannot be deleted)

Connect/as sysdba

Show user

3) enter public Mode

Connect sys/123456 as sysoper

Show user

 

 

 

ORA-12560: TNS: protocol adapter error.

In general, there are three reasons for the problem that caused the ORA-12560: TNS: protocol adapter error:

1. The listening service is not started. On the windows platform, perform the following operations: start --- program --- management tools --- Service, open the service panel, and start the oraclehome92TNSlistener service.

2. The database instance is not started. On windows, perform the following operations: start --- program --- management tools --- Service, open the service panel, start oracleserviceXXXX, and XXXX is your database SID. For example, my SID is ORCL.

3. Right-click my computer and choose "property"> "advanced"> "environment variable"> "system variable"> "new". The variable name is oracle_sid, and the variable value is XXXX. XXXX is your database SID.

The main cause is that the newly installed database instance oratest overwrites the sid of my original ORCL, and controls the sid by setting the environment variable.

2. Create a table

Enter the Same Name mode first (normal users do not have permission to create tables)

Connect scott/123

 

1) create a table with the table name productinfo

Create table productinfo (

ProductId varchar2 (10) constraint P_Id primary key,

ProductName varchar2 (10) not null,

ProductPrice number (1000) constraint P_Price check (ProductPrice between 0 and ),

Quantity number (10)

);

 

2) create a temporary table

Create global temporary table temporary_table (

ID number (2) primary key,

Name varchar2 (20)

)

On commit Delete rows;

 

3) create a table using subquery

Create table P_select (P_Id, P_Name)

As

Select ProductId, ProductName From Productinfo where ProductPrice> 10;

 

 

 

 

 

3. Table Constraints

 

1) Add a unique constraint

Alter table productinfo add constraint P_Name Unique (ProductName );

2) delete a unique constraint

Alter table productinfo drop Unique (ProductName );

3) Constraint status

Disable Constraints

Alter table productinfo disable constraint P_Price;

Activation Constraints

Alter table productinfo enable constraint P_Price;

4) query Constraints

Select constraint_name, constraint_type, deferred, status

From user_constraints

Where table_name = 'productinfo ';

'Productinfo' must be capitalized.

 

4. modify a table

1) Add a new column

Alter table productinfo

ADD (Category number (6 ));

2) modify the column type

Alter table productinfo

Modify Category varchar2 (10 );

3) modify the column name

Alter table productinfo rename column Category to Desperation;

4) Delete Columns

Alter table productinfo

Drop (Desperation );

 

5) Modify Table Parameters

Alter table productinfo

Pctfree 10 pctused 70;

 

6) Table Structure Reorganization

Alter table productinfo move;

7) rename a table

Change productinfo to product

Rename productinfo to product;

Change product to productinfo

Alter table product rename to productinfo;

8) add comments to tables and columns

Comment on table productinfo IS 'item information ';

Comment on column productinfo. ProductName IS 'item name ';

(Add a comment to the column: SQL> comment on column table name. column name is 'column annotation ';)

 

5. delete a table

Drop table productinfo;

 

Disconnect: exit

 

 

 

Appendix:

Use scott for the first time

 

Set MYDB to 123 as the new password

 

New to SYS mode. In this mode, modify Scott

 

Connect to scott and Change Password

The input characters are not displayed in the window when a new password is entered.