oracle dba 应该熟悉的命令（6）

121 高水位前移.速度快
truncate table a;
122 分配空间给表
alter table a allocate extent (datafile 'D:ORADATAMYDBusers01.dbf' size 1m);
123 指定参数建立表
create table C (a int) tablespace system pctfree 10 pctused 60 storage(freelists 2);
CREATE TABLE "SYS"."D" ( "A" VARCHAR2(10)) TABLESPACE "SYSTEM" PCTFREE 5 PCTUSED 60 INITRANS 2 MAXTRANS 100 STORAGE ( FREELISTS 2)
124 生成一个测试表
create table a tablespace users as select * from dba_objects;
125 查看表行的物理地址
select rowid form a;
126 用包的命令查看表行的物理地址
select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM A
127 建立表索引
CREATE INDEX A_IND ON A(OBJECT_NAME) TABLESPACE MYTBS2;
128 查看表的索引
SELECt * FROM DBA_INDEXES WHERE TABLE_NAME='A';
SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A_IND';
129 移动重组表空间
ALTER TABLE A MOVE TABLESPACE USERS;
130 重建表索引
ALTER INDEX A_IND REBUILD;
131 设置行可以内部移动属性
ALTER TABLE A ENABLE ROW MOVEMENT;
132 在原地进行行的移动
ALTER TABLE A SHRINK SPACE;
133 查看表所存储的信息
SELECT BYTES FORM DBA_SEGMENTS WHERE SEGMENT_NAME='A';
SELECT OWNER,SEGMENT_NAME,BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';
134 截取表
TRUNCATE TABLE A;
135 删除表
DROP TABLE A CASCADE CONSTRAINTS;
136 显示垃圾桶(10G才有)
show recyclebin
SELECT * FROM DBA_RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;
137 恢复已删除的表
flashback table a to before drop;
138 恢复较早已前的已删除的表
flashback table "BIN$hWrBq1irS0COp10n22NZJg==$0" to before drop rename to a1;
139 查询指定表的索引
select * from dba_indexs where table_name='A';
140 清除垃圾桶
PURGE RECYCLEBIN;
PURGE DBA_RECYCLEBIN;
141 删除表并清除垃圾桶(10G)
DROP TABLE A PURGE;
142 删除表的列
ALTER TABLE A DROP COLUMN COMMENTS CASCAGE CONSTRAINTES CHECKPOINT 1000;

143 重命名列
ALTER TABLE A RENAME COLUMN HIRE_DATE TO START_DATE;
144 标记不可使用的列
ALTER TABLE A SET UNUSED COLUMN COMMENTS CASCADE CONSTRAINTS;
145 删除不再使用的的列
ALTER TABLE A DROP UNUSED COLUMNS CHECKPOINT 10000;
146 继续列的删除操作
ALTER TABLE A DROP COLUMNS CONTINUE CHECKPOINT 10000;
147 增加表的主索引
ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID);
148 删除表的主索引
ALTER TABLE A DROP PRIMARY KEY;
149 索引表的主索引并指定索引所使用的表空间
ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID) USING INDEX TABLESPACE USERS;
150 建立一个普通索引
CREATE INDEX A_NAME_IND ON A(OWNER,OBJECT_NAME);