/*在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理*/ create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m segment space management auto;
alter tablespace userdata mininum extent 10;
alter tablespace userdata default storage(initial 1m next 1m pctincrease 20);
/*undo tablespace(不能被用在字典管理模下) */ create undo tablespace undo1 datafile '$ORACLE_HOME/oradata/undo101.dbf' size 40M extent management local;
show parameter undo;
/*temporary tablespace*/ create temporary tablespace userdata tempfile '$ORACLE_HOME/oradata/undo101.dbf' size 10m extent management local;
/*设置数据库缺省的临时表空间*/
alter database default temporary tablespace tablespace_name;
/*系统/临时/在线的undo表空间不能被offline*/ alter tablespace tablespace_name offline/online;
alter tablespace tablespace_name read only;
/*重命名用户表空间*/ alter tablespace tablespace_name rename datafile '$ORACLE_HOME/oradata/undo101.dbf' to '$ORACLE_HOME/oradata/undo102.dbf';
/*重命名系统表空间 ,但在重命名前必须将数据库shutdown,并重启到mount状态*/
alter database rename file '$ORACLE_HOME/oradata/system01.dbf' to '$ORACLE_HOME/oradata/system02.dbf';
drop tablespace userdata including contents and datafiles;---drop tablespce
/*resize tablespace,autoextend datafile space*/
alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M;
/*resize datafile*/
alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' resize 50m;
/*给表空间扩展空间*/ alter tablespace userdata add datafile '$ORACLE_HOME/oradata/undo102.dbf' size 10m;
/*将表空间设置成OMF状态*/
alter system set db_create_file_dest='$ORACLE_HOME/oradata';
create tablespace userdata;---use OMF status to create tablespace;
drop tablespace userdata;---user OMF status to drop tablespace;
select * from dba_tablespace/v$tablespace/dba_data_files;
/*将表的某分区移动到另一个表空间*/
alter table table_name move partition partition_name tablespace tablespace_name;
###### ORACLE storage structure and relationships #########
/*手工分配表空间段的分区(extend)大小*/
alter table kong.test12 allocate extent(size 1m datafile '$ORACLE_HOME/oradata/undo102.dbf');
alter table kong.test12 deallocate unused; ---释放表中没有用到的分区
show parameter db;
alter system set db_8k_cache_size=10m; ---配置8k块的内存空间块参数
select * from dba_extents/dba_segments/data_tablespace;
select * from dba_free_space/dba_data_file/data_tablespace;
/*数据对象所占用的字节数*/
select sum(bytes) from dba_extents where onwer='kong' and segment_name ='table_name';
############ UNDO Data ################
show parameter undo;
alter tablespace users offline normal;
alter tablespace users offline immediate;
recover datafile '$ORACLE_HOME/oradata/undo102.dbf';
alter tablespace users online ;
select * from dba_rollback_segs;
alter system set undo_tablespace=undotbs1;
/*忽略回滚段的错误提示*/
alter system set undo_suppress_errors=true;
/*在自动管理模式下,不会真正建立rbs1;在手工管理模式则可以建立,且是私有回滚段*/
create rollback segment rbs1 tablespace undotbs;
desc dbms_flashback;
/*在提交了修改的数据后,9i提供了旧数据的回闪操作,将修改前的数据只读给用户看,但这部分数据不会又恢复在表中,而是旧数据的一个映射*/
execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm');
execute dbms_flashback.disable;
/*回滚段的统计信息*/
select end_time,begin_time,undoblks from v$undostat;
/*undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24) UR :UNDO_RETENTION 保留的时间(秒) UPS :每秒的回滚数据块
DBS:系统EXTENT和FILE SIZE(也就是db_block_size)*/
select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction;
show parameter transactions;
show parameter rollback;
/*在手工管理模式下,建立公共的回滚段*/
create public rollback segment prbs1 tablespace undotbs;
alter rollback segment rbs1 online;----在手工管理模式
/*在手工管理模式中,initSID.ora中指定 undo_management=manual 、rollback_segment=('rbs1','rbs2',...)、
transactions=100 、transactions_per_rollback_segment=10 然后 shutdown immediate ,startup pfile=....\\???.ora */
########## Managing Tables ###########
/*char type maxlen=2000;varchar2 type maxlen=4000 bytes rowid 是18位的64进制字符串 (10个bytes 80 bits) rowid组成: object#(对象号)--32bits,6位 rfile#(相对文件号)--10bits,3位 block#(块号)--22bits,6位 row#(行号)--16bits,3位
64进制: A-Z,a-z,0-9,/,+ 共64个符号
dbms_rowid 包中的函数可以提供对rowid的解释*/
select
rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid) from table_name;
create table test2 (
id int,
lname varchar2(20) not null,
fname varchar2(20) constraint ck_1 check(fname like 'k%'), empdate date default sysdate) ) tablespace tablespace_name;
create global temporary table test2 on commit delete/preserve rows as select * from kong.authors;
create table user.table(...) tablespace tablespace_name storage(...) pctfree10 pctused 40; alter table user.tablename pctfree 20 pctused 50 storage(...);---changing table storage
/*手工分配分区,分配的数据文件必须是表所在表空间内的数据文件*/
alter table user.table_name allocate extent(size 500k datafile '...');
/*释放表中没有用到的空间*/
alter table table_name deallocate unused;
alter table table_name deallocate unused keep 8k;
/*将非分区表的表空间搬到新的表空间,在移动表空间后，原表中的索引对象将会不可用，必须重建*/
alter table user.table_name move tablespace new_tablespace_name;
create index index_name on user.table_name(column_name) tablespace users;
alter index index_name rebuild;
drop table table_name [CASCADE CONSTRAINTS];
alter table user.table_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT 1000];---drop column
/*给表中不用的列做标记*/
alter table user.table_name set unused column comments CASCADE CONSTRAINTS;
/*drop表中不用的做了标记列*/
alter table user.table_name drop unused columns checkpoint 1000;
/*当在drop col是出现异常，使用CONTINUE，防止重删前面的column*/ ALTER TABLE USER.TABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000;
select * from dba_tables/dba_objects;