【IT168 服务器学院】二、查看当前用户每个表占用空间的大小：
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
as4101> lsnrctl start
as4101> svrmgrl
svrmgrl> connect internal;
svrmgrl> shutdown immediate;
svrmgrl> startup
二、查看有哪些表空间
svrmgrl> SELECT * FROM DBA_TABLESPACES;
SYSTEM RBS TEMP TOOLS USERS
三、将USERS表空间DROP
svrmgrl> ALTER TABLESPACE USERS OFFLINE;
svrmgrl> DROP TABLESPACE USERS;
四、查看表空间的空余大小
svrmgrl> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME MB
------------------------------ ---------
DD_DATA 1136.3672
DD_IDX 787.18164
JX_DATA 827.94531
JX_IDX 503.16016
RBS 371.9668
SYSTEM 457.81445
TEMP 1499.9961
TOOLS 36.462891
五、查看数据文件放置的路径
svrmgrl> SELECT TABLESPACE_NAME,BYTES/1024/1024 MB,FILE_NAME
FROM DBA_DATA_FILES;
TABLESPACE_NAME MB FILE_NAME
------------------------------ --------- ---------------
SYSTEM 500 /dev/rdrd/drd4
RBS 500 /dev/rdrd/drd14
RBS 1000 /dev/rdrd/drd15
RBS 500 /dev/rdrd/drd32
TOOLS 50 /dev/rdrd/drd5
TEMP 1000 /dev/rdrd/drd22
TEMP 500 /dev/rdrd/drd23
JX_DATA 500 /dev/rdrd/drd33
六、对应SYSTEM表空间有一个回退段，为SYSTEM，另有一些回退段是属于RBS的， 先将RBS下的回退段都OFFLINE，并DROP，然后将RBS表空间DROP并重新创建，最后，创建回退段。回退段4个，每个大小为RBS/4，这个值可以当作OPTIMAL值，
即等于INITIAL+NEXT*MAXEXTENTS
svrmgrl> ALTER ROLLBACK SEGMENT R01 OFFLINE;
svrmgrl> DROP ROLLBACK SEGMENT R01;
svrmgrl> alter tablespace rbs offline;
svrmgrl> drop tablespace rbs;
svrmgrl> Create TABLESPACE "RBS" DATAFILE
''/dev/rdrd/rbs01.ora'' SIZE 500M,
''/dev/rdrd/rbs02.ora'' SIZE 500M;
svrmgrl> CREATE ROLLBACK SEGMENT "R01" TABLESPACE "RBS"
STORAGE ( INITIAL 200M NEXT 2M OPTIMAL 250M
MINEXTENTS 2 MAXEXTENTS 25);
七、查看回退段及表空间的状态,若为ONLINE，即结束，为OFFLINE，要ONLINE
svrmgrl> select SEGMENT_NAME,TABLESPACE_NAME,status from DBA_ROLLBACK_SEGS;
svrmgrl> ALTER ROLLBACK SEGMENT R01 ONLINE;
八、临时表空间TEMP，先DROP，再重建。
svrmgrl> alter tablespace temp offline;
svrmgrl> drop tablespace temp;
svrmgrl> CREATE TABLESPACE temp DATAFILE
''/dev/rdrd/drd22'' SIZE 1000M storage (initial 300m next 20m
minextens 2 maxextents 35 pctincrease 0);
九、工具表空间TOOLS大小为50M足够用，系统表空间SYSTEM为100M足够用。
十、创建数据表空间：
DD_DATA、DD_IDX、JX_DATA、JX_IDX、SF_DATA、SF_IDX、JF_DATA、JF_IDX
svrmgrl> CREATE TABLESPACE dd_data DATAFILE
''/dev/rdrd/drd9'' SIZE 1000M,
''/dev/rdrd/drd10'' SIZE 1000M,
''/dev/rdrd/drd26'' SIZE 1000M,
''/dev/rdrd/drd35'' SIZE 1000M,
''/dev/rdrd/drd42'' SIZE 500M;
十一、创建用户
svrmgrl> CREATE USER ddbh IDENTIFIED BY ddbh
DEFAULT TABLESPACE dd_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON dd_data
QUOTA UNLIMITED ON dd_idx
QUOTA UNLIMITED ON rbs
QUOTA UNLIMITED ON temp;
十二、用户权限
svrmgrl> grant connect,resources,imp_full_database,exp_full_database,
create public synonym,drop public synonym to ddbh;
若要查看V$SESSION，KILL SESSION, DROP USER,CREATE USER等,则
svrmgrl> grant select on v_$session to public;
svrmgrl> grant alter system,drop user,create user to "*******";
表空间文件破坏恢复
startup mount;
alter database datafile ''/home/oracle/dbs3/bill_part200507.dat'' offline drop;
alter database open;
drop tablespace bill_part200507 including contents;
rm /home/oracle/dbs3/bill_part200507.dat
关于exp,imp
exp cba/pqlamz file=(old01.dmp,old02.dmp,old03.dmp,old04.dmp) tables=''TBLHISMOBILESENDSMS,TBLHISUNICOMSENDSMS,TBLHISRECVSMS'' filesize=104857600 direct=y
exp cba/pqlamz file=SENDSMS200404.dmp tables=(TBLPARTHISUNICOMSENDSMS:SENDSMS200404) direct=y
exp cba/pqlamz file=SENDSMS200405.dmp tables=(TBLPARTHISUNICOMSENDSMS:SENDSMS200405) direct=y
exp cba/pqlamz file=SENDSMS200406.dmp tables=(TBLPARTHISUNICOMSENDSMS:SENDSMS200406) direct=y
建立分区表及注意问题
用户要指定表空间，临时表空间 保证回滚表空间
建立表空间 最好svrmgr登陆后建立
--create user bill identified by pqlamz default tablespace users Temporary TABLESPACE temp;
conn bill/pqlamz;
--tblbill分区表
CREATE TABLESPACE bill_part200507
DATAFILE ''/home/oracle/dbs3/bill_part200507.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200508
DATAFILE ''/home/oracle/dbs3/bill_part200508.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200509
DATAFILE ''/home/oracle/dbs3/bill_part200509.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200510
DATAFILE ''/home/oracle/dbs3/bill_part200510.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200511
DATAFILE ''/home/oracle/dbs3/bill_part200511.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200512
DATAFILE ''/home/oracle/dbs3/bill_part200512.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200601
DATAFILE ''/home/oracle/dbs3/bill_part200601.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200602
DATAFILE ''/home/oracle/dbs3/bill_part200602.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200603
DATAFILE ''/home/oracle/dbs3/bill_part200603.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200604
DATAFILE ''/home/oracle/dbs3/bill_part200604.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200605
DATAFILE ''/home/oracle/dbs3/bill_part200605.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE bill_part200606
DATAFILE ''/home/oracle/dbs3/bill_part200606.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
PROMPT Drop Table tblbill
DROP TABLE tblbill cascade constraints;
create table tblbill --广播发送整个bsc表
(
id number(20) default 0 not null,
phoneNum Varchar2(21) default '' '' not null,
SmgId number(5) default 0 not null,
city Varchar2(20) default '' '' not null,
FeeValue number(5) default 0 not null, --费用
ServiceId Varchar2(8) default ''ERROR'' not null, --计费字段
CreateTime date default sysdate not null,
SendTime date default sysdate not null,
status number(4) default -2 not null, --0：帐单成功 -1:按条成功,>0:失败状态
TYPE NUMBER(4) default 0 NOT NULL
)PARTITION BY RANGE (CreateTime)
(
PARTITION bill_part200507 VALUES LESS THAN (TO_DATE(''2005-08-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200507,
PARTITION bill_part200508 VALUES LESS THAN (TO_DATE(''2005-09-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200508,
PARTITION bill_part200509 VALUES LESS THAN (TO_DATE(''2005-10-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200509,
PARTITION bill_part200510 VALUES LESS THAN (TO_DATE(''2005-11-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200510,
PARTITION bill_part200511 VALUES LESS THAN (TO_DATE(''2005-12-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200511,
PARTITION bill_part200512 VALUES LESS THAN (TO_DATE(''2006-01-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200512,
PARTITION bill_part200601 VALUES LESS THAN (TO_DATE(''2006-02-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200601,
PARTITION bill_part200602 VALUES LESS THAN (TO_DATE(''2006-03-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200602,
PARTITION bill_part200603 VALUES LESS THAN (TO_DATE(''2006-04-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200603,
PARTITION bill_part200604 VALUES LESS THAN (TO_DATE(''2006-05-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200604,
PARTITION bill_part200605 VALUES LESS THAN (TO_DATE(''2006-06-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200605,
PARTITION bill_part200606 VALUES LESS THAN (TO_DATE(''2006-07-01'',''YYYY-MM-DD'')) TABLESPACE bill_part200606
);
--序列
Drop Sequence sqbill_id;
PROMPT Creating Sequence for tblbill
create sequence sqbill_id start with 1 maxvalue 9999999999999 CYCLE;
--索引()
CREATE TABLESPACE idx_bill
DATAFILE ''/home/oracle/dbs3/bill_idx.dat''
SIZE 600M AUTOEXTEND ON NEXT 10M MAXSIZE 800M
DEFAULT STORAGE (INITIAL 100m NEXT 10m --注意在此空间每建立一个索引都至少需要一个initial值
MINEXTENTS 1 PCTINCREASE 0) ;
alter table tblbill add PRIMARY KEY (Id) USING INDEX tablespace idx_bill;
drop index idx_tblbill_SendTime;
drop index idx_tblbill_serviceId ;
drop index idx_tblbill_phonenum ;
create index idx_tblbill_SendTime on tblbill(SendTime) tablespace idx_bill nologging;
create index idx_tblbill_serviceId on tblbill(serviceId) tablespace idx_bill nologging;
create index idx_tblbill_phonenum on tblbill(phonenum,SendTime) tablespace idx_bill nologging;
--tblbillLog分区表
CREATE TABLESPACE billLog_part200507
DATAFILE ''/home/oracle/dbs3/billLog_part200507.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200508
DATAFILE ''/home/oracle/dbs3/billLog_part200508.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200509
DATAFILE ''/home/oracle/dbs3/billLog_part200509.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200510
DATAFILE ''/home/oracle/dbs3/billLog_part200510.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200511
DATAFILE ''/home/oracle/dbs3/billLog_part200511.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200512
DATAFILE ''/home/oracle/dbs3/billLog_part200512.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200601
DATAFILE ''/home/oracle/dbs3/billLog_part200601.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200602
DATAFILE ''/home/oracle/dbs3/billLog_part200602.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200603
DATAFILE ''/home/oracle/dbs3/billLog_part200603.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200604
DATAFILE ''/home/oracle/dbs3/billLog_part200604.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200605
DATAFILE ''/home/oracle/dbs3/billLog_part200605.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
CREATE TABLESPACE billLog_part200606
DATAFILE ''/home/oracle/dbs3/billLog_part200606.dat''
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 200M
DEFAULT STORAGE (INITIAL 100m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
PROMPT Drop Table tblbillLog
DROP TABLE tblbillLog cascade constraints;
create table tblbillLog
(
id number(20) default 0 not null,
BillId number(20) default 0 not null,
report number(5) default 0 not null, --短信从网关返回的状态报告0:成功
SendTime date default sysdate not null
)PARTITION BY RANGE (SendTime)
(
PARTITION billLog_part200507 VALUES LESS THAN (TO_DATE(''2005-08-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200507,
PARTITION billLog_part200508 VALUES LESS THAN (TO_DATE(''2005-09-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200508,
PARTITION billLog_part200509 VALUES LESS THAN (TO_DATE(''2005-10-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200509,
PARTITION billLog_part200510 VALUES LESS THAN (TO_DATE(''2005-11-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200510,
PARTITION billLog_part200511 VALUES LESS THAN (TO_DATE(''2005-12-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200511,
PARTITION billLog_part200512 VALUES LESS THAN (TO_DATE(''2006-01-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200512,
PARTITION billLog_part200601 VALUES LESS THAN (TO_DATE(''2006-02-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200601,
PARTITION billLog_part200602 VALUES LESS THAN (TO_DATE(''2006-03-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200602,
PARTITION billLog_part200603 VALUES LESS THAN (TO_DATE(''2006-04-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200603,
PARTITION billLog_part200604 VALUES LESS THAN (TO_DATE(''2006-05-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200604,
PARTITION billLog_part200605 VALUES LESS THAN (TO_DATE(''2006-06-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200605,
PARTITION billLog_part200606 VALUES LESS THAN (TO_DATE(''2006-07-01'',''YYYY-MM-DD'')) TABLESPACE billLog_part200606
);
--序列
Drop Sequence sqbillLog_id;
PROMPT Creating Sequence for tblbillLog
create sequence sqbillLog_id start with 1 maxvalue 9999999999999 CYCLE;
--索引
CREATE TABLESPACE idx_billLog
DATAFILE ''/home/oracle/dbs3/billLog_idx.dat''
SIZE 600M AUTOEXTEND ON NEXT 10M MAXSIZE 800M
DEFAULT STORAGE (INITIAL 200m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
alter table tblbillLog add PRIMARY KEY (Id) USING INDEX tablespace idx_billLog;
create index idx_tblbillLog_billid on tblbillLog(billid) tablespace idx_billLog nologging;
增加分区
CREATE TABLESPACE send_sms_his200509
DATAFILE ''/home/oracle/dbs3/send_sms_his200509.dat''
SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE 500M
DEFAULT STORAGE (INITIAL 200m NEXT 10m
MINEXTENTS 1 PCTINCREASE 0) ;
ALTER TABLE tblhissendsms ADD PARTITION send_sms_his200509 VALUES LESS THAN (TO_DATE(''2005-10-01'',''YYYY-MM-DD'')) TABLESPACE send_sms_his200509;
删除分区
alter table TBLPARTHISUNICOMSENDSMS truncate partition SENDSMS200406;
select count(*) from TBLPARTHISUNICOMSENDSMS partition(SENDSMS200406);
