INDEX知识点归纳

最新推荐文章于 2023-12-23 20:25:38 发布

congjiu9273

最新推荐文章于 2023-12-23 20:25:38 发布

阅读量97

点赞数

一：查看表：

sql>desc user_tables;

sql>select table_name,tablespace_name from user_tables;

二：查看表结构的完整定义信息

sql>select dbms_metadata.getddl(‘TABLE’, ‘T’) from dual;

注意：在oracle 9i中要打一个patch.

三．将表t移动到mssm表空间中

alter table t move tablespace mssm;

四：truncate table

The effects of using this command are as follows:

All rows in the table are deleted.

No undo data is generated and the command commits implicitly because

TRUNCATE TABLE is a DDL command.

Corresponding indexes are also truncated.

A table that is being referenced bya foreign key cannot be truncated.

The delete triggersdo not fire when this command is used.

官方文档关于truncate的论述：

TRUNCATE

Caution:

You cannot roll back aTRUNCATEstatement.

Purpose

Use theTRUNCATEstatement to remove all rows from a table or cluster. By default, Oracle Database also performs the following tasks:

· Deallocates all space used by the removed rows except that specified by theMINEXTENTSstorage parameter

· Sets theNEXTstorage parameter to the size of the last extent removed from the segment by the truncation process

Removing rows with theTRUNCATEstatement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to re-grant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and re-specify its storage parameters. Truncating has none of these effects.

Removing rows with theTRUNCATEstatement can be faster than removing all rows with theDELETEstatement, especially if the table has numerous triggers, indexes, and other dependencies.

五：Dropping a Table

DROP TABLE hr.departments

CASCADE CONSTRAINTS;

语法：

DROP TABLE [schema.] table

[CASCADE CONSTRAINTS]

When a table is dropped, the extents used by the table are released. If they are contiguous, they may be coalesced（融合） either automatically or manually at a later stage.

The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

六：Dropping a Column

Removing a column from a table:

ALTER TABLE hr.employees

DROP COLUMN comments

CASCADE CONSTRAINTS CHECKPOINT 1000;

Chechpoint nnn意思是删除多少之后产生一次undo（每1000行产生一次chekpoint），会减少性能影响

Removes the column length and data from each row,

freeing space in the data block.

Dropping a column in a large table takes a

considerable amount of time.

七: Renaming a Column

Renaming a column from a table:

ALTER TABLE hr.employees

RENAME COLUMN hire_date

TO start_date;

语法：

ALTER TABLE [schema.]table_name

RENAME COLUMN old_column_name

TO new_column_name;

八：Using the UNUSED Option

Mark a column as unused:

ALTER TABLE hr.employees

SET UNUSED COLUMN comments CASCADE

CONSTRAINTS;

Drop unused columns:

ALTER TABLE hr.employees

DROP UNUSED COLUMNS CHECKPOINT 1000;

Continue to drop column operation:

ALTER TABLE hr.employees

DROP COLUMNS CONTINUE CHECKPOINT 1000;

Instead of removing a column from a table, the column can be marked as unused and then removed later. This has the advantage of being relatively quick, because it does not reclaim the disk space because the data is not removed. Columns that are marked as unused can be removed at a later time from the table when there is less activity on the system. Unused columns act as if they are not partof the table. Queries cannot see data from unused columns. In addition, the names and data types of those columns are not displayed when a DESCRIBE command is executed. A user can add a new column with the same name as an unused column.

An example of setting a column to unused before dropping it is when you want to drop two columns in the same table. When you drop two columns, all rows in the table are updated twice. But, when you set the columns to unused and then drop the columns, the rows are updated only once.

You cannot do the following:

Drop a column from an object type table

Drop columns from nested tables

Drop all columns in a table

Drop a partitioning key column

查看有哪些unused column,注意这里要用sys用户查看

sql>SELECT * FROM dba_unused_col_tabs;

查看drop column过程中有未完成的

sql>SELCT * FROM dba_partial_drop_tabs;

怎么样查看用户默认表空间

select username,default_tablespace from dba_users;

怎么样设置默认表空间