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

A basic maintenance note for oracle tables and objects

1.1 Common concepts

1.2 create a table

1.3 Table common fields

1.4 add or remove fields

1.5 Update Field

1.6 rename table

1.7 change table storage table space and storage parameters

1.8 Delete table

1.9 Table Notes

1.10 Management of partition tables

1.11 Common data dictionary

Two constraints

2.1 non-null constraint

2.2 Primary key constraint

2.3 uniqueness constraint

2.4 Foreign key constraint

2.5 constraint Management

Three indexes

3.2 create an index

3.3 change index storage parameters

3.4 re-index

3.5 Index defragmentation

3.6 Delete the index

3.7 data dictionary

Four views

4.1 create a view

4.2 View authorization

4.3 Delete a view

Five synonyms

5.1 create synonyms

5.2 remove synonyms

Six sequences

6.1 establish a sequence

6.2 Delete sequence

1.1 Common concepts

Table naming convention: no more than 30 words, only numbers, letters, _, #, $

1.2 create a table

Create table [schema.] table (column datatype [default expr])

Create a table using a subquery

Create table table

[(column,column...)]

As subquery

-create a table

Create table cw1 (

Name varchar2 (25) not null

Id number

)

Insert into cw1 (name,id) values ('cw',1)

Create table cw2

As select * from cw1

1.3 Table common fields

Varchar2 (size) up to 4000 bytes

Char (size) up to 2000 bytes

Number (p [, s]) p total length, s decimal places

Date

Long can be up to 2G

CLOB can be up to 4G

RAW and LONG RAW binary data, up to 2000 bytes, 2G

BLOB binary data, up to 4G

BFILE stores binary data of external files, up to 4G

ROWID line address

Create table cw3 (

Name varchar2 (10)

Sex char (4)

Deptid number (10)

Create_date date

Card_id long

Picture blob

File_id bfile)

-- insert data

Insert into cw2 (name,id,age,cardid) values ('cw1',3,19,'12345')

Insert into cw2 (name,id,age,cardid) values ('cw1',4,20,'242345')

Insert into cw2 (name,id,age,cardid) values ('cw1',5,21,'123322225')

Insert into cw2 (name,id,age,cardid) values ('cw1',6,22,'1242234545')

Insert into cw2 (name,id,age,cardid) values ('cw1',7,23,'1252342345')

Insert into cw2 (name,id,age,cardid) values ('cw1',8,24,'124234245')

Insert into cw2 (name,id,age,cardid) values ('cw1',9,25,'133223445')

Insert into cw2 (name,id,age,cardid) values ('cw1',10,26,'32123345')

1.4 add or remove fields

Alter table employees add (age number (2))

-- add field

Alter table cw2 add (age number (2))

Alter table cw2 add (cardid varchar (10))

-- deleting a field

Alter table cw2 drop (cardid)

1.5 Update Field

Alter table table_name modify column_name type

-- change table fields:

Alter table cw2 modify (age char (10))

-if there is data in the table and needs to be modified to other types of data, an error will be reported.

SQL > alter table cw2 modify (age char (10))

Alter table cw2 modify (age char (10))

ORA-01439: column to be modified must be empty to change datatype

SQL >

-it is normal to change the same data type.

SQL > alter table cw2 modify (age number (10))

Table altered

SQL >

Note: if you need to modify the data field type, you need to process the data in the table before changing the type.

1. This method can meet the demand because the new fields are added to the end of the table by default, and row migration may occur, which will have an impact on the application.

two。 The second method is to add a column with the same column type as the column to be modified, then copy the data of the modified column to the new column and empty the column to be modified, then modify the data type, and then copy the data back from the new column, this process involves two times of data replication, if it is a large amount of data, it will be relatively slow at the same time, it will produce a lot of undo and redo; advantage is that the data will not occur row migration.

1.6 rename table

Alter table XXX RENAME to xxxxx

-rename table

SQL > alter table cw2 rename to cw4

Table altered

SQL >

-if you have schema, you will get an error, and you need to remove the following schema

SQL > alter table system.cw4 rename to system.cw2

Alter table system.cw4 rename to system.cw2

ORA-14047: ALTER TABLE | INDEX RENAME may not be combined with other operations

SQL >

-change with schema as follows:

SQL > alter table system.cw4 rename to cw2

Table altered

SQL >

1.7 change table storage table space and storage parameters

1.8 Delete table

Delete table: drop table_name

SQL > drop table system.cw2

Table dropped

SQL >

Delete table data:

Truncate

Delete

Difference: 1.truncate cannot rollback

2.truncate cannot trigger any delete triggers

-delete deletes data

SQL > select * from cw3

NAME ID AGE CARDID

--

Cw1 3 19 12345

Cw1 4 20 242345

Cw1 5 21 123322225

Cw1 6 22 1242234545

Cw1 7 23 1252342345

Cw1 8 24 124234245

Cw1 9 25 133223445

Cw1 10 26 32123345

8 rows selected

SQL > delete from cw3

8 rows deleted

SQL > select * from cw3

NAME ID AGE CARDID

--

SQL > rollback

Rollback complete

SQL > select * from cw3

NAME ID AGE CARDID

--

Cw1 3 19 12345

Cw1 4 20 242345

Cw1 5 21 123322225

Cw1 6 22 1242234545

Cw1 7 23 1252342345

Cw1 8 24 124234245

Cw1 9 25 133223445

Cw1 10 26 32123345

8 rows selected

SQL >

-delete

-truncate deletes data

SQL > truncate table cw3

Table truncated

SQL > rollback

Rollback complete

SQL > select * from cw3

NAME ID AGE CARDID

--

SQL >

-truncate

1.9 Table Notes

Comment on table employees IS 'test'

-add table test records

SQL > comment on table cw3 is' Test'

Comment added

SQL > desc cw3

Name Type Nullable Default Comments

--

NAME VARCHAR2 (20) Y

ID NUMBER (5) Y

AGE CHAR (5) Y

CARDID VARCHAR2 (10) Y

SQL > select * from dba_tab_comments where table_name='CW3'

OWNER TABLE_NAME TABLE_TYPE COMMENTS

- -

SYSTEM CW3 TABLE test

SQL >

1.10 Management of partition tables

There are some points in the partition table:

The partitioning method of the partition table:

Range Partition:

Hash partition

List partition

Compound partition (range + hash) (range + list)

Create table cw_part1 (

Name varchar (20)

Id number (5)

Age char (5)

Cardid varchar (10))

Partition by range (age)

(partition age_1 values less than (22)

Partition age_2 values less than (24)

Partition age_3 values less than (26))

As select name,id,age,cardid from cw2

-Partition table

SQL > select * from cw_part partition (age_3)

NAME ID AGE CARDID

--

SQL > insert into cw_part (name,id,age,cardid) values ('cw2',11,25,'232432')

1 row inserted

SQL > select * from cw_part partition (age_3)

NAME ID AGE CARDID

--

Cw2 11 25 232432

SQL > select * from cw_part partition (age_1)

NAME ID AGE CARDID

--

Cw2 11 20 232432

SQL > select * from cw_part partition (age_2)

NAME ID AGE CARDID

--

SQL > select * from cw_part partition (age_3)

NAME ID AGE CARDID

--

Cw2 11 25 232432

-Partition table

1.11 Common data dictionary

All_col_comments

User_col_comments

All_tab_comments

User_tab_comments

Two constraints

2.1 non-null constraint

Not null

-- add non-empty

SQL > alter table cw2 add (key varchar (2) not null)

Alter table cw2 add (key varchar (2) not null)

ORA-01758: table must be empty to add mandatory (NOT NULL) column

SQL > select * from cw1

NAME ID

--

Cw 1

SQL > truncate cw1

Truncate cw1

ORA-03290: Invalid truncate command-missing CLUSTER or TABLE keyword

SQL > truncate table cw1

Table truncated

-if the table is empty, it can be added successfully

SQL > alter table cw1 add (key varchar (2) not null)

Table altered

SQL >

-

2.2 Primary key constraint

Primary key

Create table cw (name varchar constraint pk_name primary key,id number)

-add primary key

SQL > alter table cw2 modify (id number (5) primary key)

Table altered

SQL > select * from cw2

NAME ID AGE CARDID

--

Cw1 3 19 12345

Cw1 4 20 242345

Cw1 5 21 123322225

Cw1 6 22 1242234545

Cw1 7 23 1252342345

Cw1 8 24 124234245

Cw1 9 25 133223445

Cw1 10 26 32123345

8 rows selected

SQL > insert into cw2 (id) values (5)

Insert into cw2 (id) values (5)

ORA-00001: unique constraint (SYSTEM.SYS_C006975) violated

SQL >

-

2.3 uniqueness constraint

Unique

Create table cw (name varchar 2 (20))

Id number

Constraint unique_name unique (name)

);

-unique index

SQL > create unique index unique_age on cw2 (age)

Index created

SQL > insert into cw2 (id,age) values (11pm 20)

Insert into cw2 (id,age) values

ORA-00001: unique constraint (SYSTEM.UNIQUE_AGE) violated

SQL >

-

2.4 Foreign key constraint

Foreign key

Create table cw (

Id number

Name varchar2 (20)

Constraint fk_cw foreign key (id) references dept (id)

);

2.5 constraint Management

Modify

Alter table cw drop constraint unique_name

Alter table cw add constraint unique_name unique (name)

Stop enabling

Alter table cw disable constraint unique_name

Alter table cw enable constraint constraint_name

-constraint stop

-

Three indexes

3.1 Overview of Index

A little bit of the index:

Speed up queries, reduce Io operations, and eliminate disk sorting

Type of index:

Unique index

Bitmap index

Hash index

Functional index

3.2 create an index

When you create an index, you need to develop index parameters

Create index index_name on table_name (field_name)

Tablespace tablespace_name

Pctfree 5

Initrans 2

Maxtrans 255

Storage

(

Minextents 1

Maxextents 16382

Pctincrease 0

)

Tablespace tablespace-specifies the tablespace pctfree 5 in which the object is created

-- the block reserves 5% space for future data updates

Initrans 2-initialize transaction slots

Maxtrans 255Max transaction slots

Below the storage-- are the storage parameters

Initial 64K-initializes the extension to 64k next 1m

-- expand 1m next time

Minextents 1-the number of cells is 1

Maxextents 16382-unlimited maximum number of zones)

Create a unique index

Create unique index dept_unique_idx on dept (dept_no) tablespace idx_data

Create a bitmap index:

Create bitmap index idx_bitm on cw (id) tablespace idx_data

Create a functional index:

Create index idx_fun on emp (upper (ename)) tablespace idx_data

3.3 change index storage parameters

Alter index unique_name

Pctfree 30

Storage (next 200k pctincrease 20)

3.4 re-index

Alter index unique_name rebuild tablespace indx

-rebuild the index-

SQL > alter index unique_age rebuild

Index altered

SQL >

-

3.5 Index defragmentation

Alter index cw_id_idx coalesce

-defragmentation

SQL > alter index unique_age coalesce

Index altered

SQL >

-

3.6 Delete the index

Drop index hr.deptartments_name_idx

It should be noted that if there is a foreign key, it cannot be deleted, and you need to prohibit the foreign key first and then delete it.

Drop table cw cascade constraints

Truncate cw stores

Alter table cw disable constraint fk_cw

-

SQL > drop index unique_age

Index dropped

SQL >

-

3.7 data dictionary

Dba_indexes

Dba_ind_columns

Dba_ind_expressions

V$object_usage

Four views

4.1 create a view

Create view temp_cw as select * from cw

You can create a system

Create view temp_cw as select * from cw

With read only

4.2 View authorization

Grante create view to chenwei

4.3 Delete a view

Drop view cw

Five synonyms

Advantages of synonyms:

Simplify SQL statement

Hide the name and owner of the object

Provide public access to objects

Divided into: public synonyms, private synonyms

5.1 create synonyms

Create public synonym table_name from chenwei.cw

5.2 remove synonyms

Drop public synonym chenwei.cw

Six sequences

6.1 establish a sequence

Create sequence seq_cw

Increment by 10

Start with 10

Minvalue 10 nomaxvalue

Query sequence:

Select seq_cw.nextval from dual

Access sequence:

Current value: CURRVAL

Next: NEXTVAL

6.2 Delete sequence

Drop sequence seq_cw

6.3 modify sequence

Alter sequence cw_seq maxvalue 5000 cycle

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.