ALTER SYSTEM CHECKPOINT;
87 显示重做日期的历史记录 select * from v$log_history;
88 更改会语的日期格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
89 增加日志组
ALTER DATABASE ADD LOGFILE GROUP 4 ( 'D:ORADATAMYDBredo4.log') SIZE 10240K;
92 删除日志组
ALTER DATABASE DROP LOGFILE GROUP 4 ;
90 增加日志组成员
ALTER DATABASE ADD LOGFILE MEMBER 'D:ORADATAMYDBredo11.log' TO GROUP 1;
91 删除日志组成员
ALTER DATABASE DROP LOGFILE MEMBER 'D:ORADATAMYDBredo14.log' ;
92 显示系统表空间
select * from dba_tablespaces;
93 显示系统表空间所拥有的文件 select * from dba_data_files;
94 显示系统临时表空间 select * from dba_temp_files;
95 显示表空间的已被使用多少
select tablespace_name,sum(bytes)/1024/1024 m from dba_data_files group by tablespace_name;
96 显示表空间还有多少没有使用 select tablespace_name,
sum(bytes)/1024/1024 m
from dba_free_space group by tablespace_name;
97 创建表空间
10G CREATE BIGFILE TABLESPACE \DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
9I CREATE TABLESPACE \DATAFILE 'D:oracleORADATAthwerpmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE SMALLFILE TABLESPACE \DATAFILE 'D:ORADATAMYDBmytbs_01.dbf' SIZE 100M , 'D:ORADATAMYDBmytbs02.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
98 在指字表空间里建表
create table t1 (a int) tablespace mytbs1;
99 在指字表空间里建索引
create index t1_ind on t1(a) tablespace mytbs1;
100 在指定表空间里增加数据文件
ALTER TABLESPACE \ADD DATAFILE 'D:ORADATAMYDBmytbs03.dbf' SIZE 100M
100 修改表空间的大小
ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS_01.DBF' RESIZE 200M
101 表空间改文件的自动扩展
ALTER DATABASE DATAFILE 'D:ORADATAMYDBMYTBS03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
102 删除表空间和表空间所属文件
drop tablespace mytbs1 including contents and datafiles;
103 建立临时表空间
CREATE SMALLFILE TEMPORARY TABLESPACE \TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M CREATE TEMPORARY TABLESPACE \TEMPFILE 'D:oracleORADATAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
104 更改用户的临时表空间
ALTER USER \ TABLESPACE \
105 设置成默认的表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE \
106 创建临时表空间组
ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUP ALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP
107 创建重做表空间
CREATE UNDO TABLESPACE \DATAFILE 'D:ORADATAMYDBmyundo01.dbf' SIZE 200M
108 显示重做参数 show parameter undo
109 回滚段的使用情况 select * from dba_rollback_segs;
110 更改重做表空间
alter system set undo_tablespace=myundo;
111 建立一个表
create table a tablespace users as select * from dba_objects;
112 插入一些记录 insert into a select * from a;
113 有条件查询表名放在哪个表空间
select * from dba_tables where table_name='A' and owner='SYS';
114 有条件查询段名放在哪个表空间
select * from dba_segments where segment_name='A' and owner='SYS';
115 查询有哪些段类型
select distinct segment_type from dba_segments;
116 查询段和表空间的对应关系
select * from dba_extents where segment_name='A' and owner='SYS';
117 建立一个16K表空间 CREATE TABLESPACE \DATAFILE 'D:oracleORADATAthwerpmytbs3.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384;
118 指字参数建立表
create table b (a int,b varchar2(10)) tablespace users INITRANS 2 PCTFREE 5;
119 显示当前所使用的块大小 show parameter block_size;
120 释放表的高水位的空间
alter table a deallocate unused;
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 \( \VARCHAR2(10)) TABLESPACE \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 \
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;