1.oracle创建表空间
create tablespace “tablespace_name” datafile 'D:\oracle\product\10.2.0\oradata\orcl\tbspace.dbf' size 1024M --存储地址 初始大小1G autoextend on next 10M maxsize unlimited --每次扩展10M,无限制扩展 EXTENT MANAGEMENT local autoallocate segment space management auto; -- 创建临时表空间 create temporary tablespace tablespace_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
2.如果想用现有用户给表空间授权
-- 1.在特定情况下我们需要Oracle拥有多个表空间,并且用用个一用户来管理这时我们使用命令: ALTER USER csdn QUOTA UNLIMITED ON SPACE2; -- 可以将SPACE2这个表空间分配给csdn这个用户来管理。 -- 2.另外,命令: ALTER USER csdn DEFAULT TABLESPACE SPACE2; /*可以修改csdn的默认表空间 值得注意的是,一个用户可以同时管理很多表空间,但是默认表空间只有一个,当创建表,视图,引索等不指定表空间时都会存放在默认表空间中。*/
参考:Oracle创建多表空间和用户,同一用户管理多个表空间_熊铁树的博客-CSDN博客_一个oracle用户 添加多个表空间
3.查看表空间,见底下第五条,清理表空间中的内容.
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files select * FROM dba_data_files
2.计划任务
参考Oracle定时执行计划任务_用户2616006187_新浪博客
用的dbms_scheduler.create_job 来创建的,也可以用dbms_job.submit。
create table job_test(updatetime date); begin dbms_scheduler.create_job ( job_name => '测试', job_type => 'PLSQL_BLOCK', job_action => 'insert into job_test values(sysdate);', start_date => sysdate, end_date => add_months(sysdate,12000), repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', enabled => true, comments => '定时执行计划任务' ); end;
•REPEAT_INTERVAL :指定任务执行的频率,比如多长时间会被触发再次执行。本参数也可以为空,如果为空的话,就表示当前设定的任务只执行一次。REPEAT_INTERVAL参数需要好好说说,REPEAT_INTERVAL参数的语法结构要复杂的多。其中最重要的是FREQ和INTERVAL两个关键字。
◦FREQ 关键字用来指定间隔的时间周期,可选参数有:YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY,分别表示年、月、周、日、时、分、秒等单位。
◦INTERVAL 关键字用来指定间隔的频率,可指定的值的范围从1-99。
例如:REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1';表示每天执行一次,如果将INTERVAL改为7就表示每7天执行一次,效果等同于FREQ=WEEKLY;INTERVAL=1。-- 查看计划任务schedule select * from user_scheduler_jobs; select JOB_NAME,JOB_ACTION,LAST_START_DATE,NEXT_RUN_DATE from user_scheduler_jobs;-- 主要看这几个字段
3.SysDate函数使用方法:
select sysdate from dual; -- 得到当天凌晨0点0分0秒的日期 select trunc(sysdate) from dual; --加法 select sysdate,add_months(sysdate,12) from dual; --加1年 select sysdate,add_months(sysdate,1) from dual; --加1月 select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期 select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天 select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时 select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟 select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒 --减法 select sysdate,add_months(sysdate,-12) from dual; --减1年 select sysdate,add_months(sysdate,-1) from dual; --减1月 select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期 select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天 select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时 select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟 select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒
4. 表空间清理
针对oracle提示表空间无法扩展的问题。
参考:Oracle 表空间清理及表空间扩容_sdzqlrz的博客-CSDN博客_oracle表空间清理及扩容
1、先查询表空间使用情况
--查询表空间使用情况 SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小", total / (1024 * 1024 * 1024) "表空间大小(G)", free / (1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;
2、某些表占用的空间较大,好针对性清理这些占用大的表
--查询表所占空间大小 select segment_name "表名", bytes/(1024 * 1024) "占用(M)" from user_segments where segment_type = 'TABLE' ORDER BY "占用(M)" DESC;
select b.file_id 文件ID, b.tablespace_name 表空间, b.file_name 物理文件名, b.bytes 总字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name
3、清理可以有drop表、truncate截断表数据、delete删除数据
清空表:delete from tabletest;
清空表只是清空表中的逻辑数据,但是物理数据不清除,如主键值、索引等不被清除,还是原来的值。
截断表:truncate table tabletest;--tabletest是表名
截断表可以用于删除表中 的所有数据。截断表命令还会回收所有索引的分配页。截断表的执行速度与不带where子句的delete(删除)命令相同,甚至比它还要快。 delete(删除)一次删除一行数据,并且将每一行被删除的数据都作为一个事务记录日志;而truncate (截断)表则回收整个数据页,只记录很少的日志项。delete(删除)和truncate(截断)都会回收被数据占用的空间,以及相关的索引。只有表的 拥有者可以截断表。
另外,truncate表之后,如果有自动主键的话,会恢复成默认值。
4、delete和truncate有时候并不能释放表空间,需要执行以下脚本释放
--收缩表空间,tabletest是表名 alter table tabletest enable row movement; alter table tabletest shrink space compact; alter table tabletest shrink space; alter table tabletest disable row movement;
5、一个数据文件最大只能32G;
其他操作
--查询所有垃圾表
select * from recyclebin where type='TABLE';
-- 回收
PURGE RECYCLEBIN;
5、表空间扩容
方式一:手动改变已存在的数据文件的大小,一个数据文件最大只能32G;
--手工改变已存在数据文件的大小 ALTER TABLESPACE app_data ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 20480M;
方式二:允许已存在的数据文件自动增长
--允许已存在的数据文件自动增长 ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;
方式三:增加数据文件
--增加数据文件 ALTER TABLESPACE SYSTEM ADD DATAFILE 'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\O2_MF_SYSTEM_CWMNZ9XV_.DBF' size 7167M autoextend on ; ALTER TABLESPACE SYSTEM ADD DATAFILE 'C:\APP\ORACLE\ORADATA\DFYYCDB\DATAFILE\O3_MF_SYSTEM_CWMNZ9XV_.DBF' size 7167M autoextend on ;