oracle9i学习笔记

_segs;

/*限制用户在某表空间的使用限额*/
alter user user_name quota 10m on tablespace_name;

create tablespace xxx [datafile 'path_name/datafile_name'] [size xxx] [extent management local/dictionary] [default storage(xxx)];

exmple: create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management dictionary default storage(initial 100k next 100k pctincrease 10) offline;
/*9i以后，oracle建议使用local管理，而不使用dictionary管理，因为local采用bitmap管理表空间 ，不会产生系统表空间的自愿争用;*/
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m;
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local autoallocate;
/*在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理*/
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 kon

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页    >>