Oracle数据库常用操作
创建表空间、用户、授权
--创建数据表空间 （查询文件路径：select * from dba_data_files;）
create tablespace 表空间名 datafile '表空间文件路径' size 1024m autoextend on next 32m extent management local;
--创建用户并指定表空间
create user 用户名 identified by 密码 default tablespace 表空间名;
--给用户授予权限
grant connect,resource to 用户名;
grant dba to 用户名;
删除USER。
DROP USER XX CASCADE
删除表空间。
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
删除空的表空间，不包含物理文件。
DROP TABLESPACE tablespace_name;
删除空表空间，包含物理文件。
DROP TABLESPACE tablespace_name INCLUDING DATAFILES;
删除非空表空间，不包含物理文件。
DROP TABLESPACE tablespace_name INCLUDING DATAFILES;
删除非空表空间，包含物理文件。
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
用delete或truncate删除所有表的内容
SELECT 'DELETE FROM '|| table_name || ';' FROM USER_TABLES
ORDER BY TABLE_NAME;或
SELECT 'TRUNCATE TABLE '|| table_name || ';' FROM USER_TABLES
ORDER BY TABLE_NAME;
注意：若表的数据有触发器相关联，只能用truncate语句，但truncate语句不能回滚。
oracle 锁表，解锁，批量解锁
查看被锁的表
SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,b.os_user_name
FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr AND a.process = b.process AND c.object_id = b.object_id；
查看是哪个进程锁的
SELECT sid, serial#, username, status, osuser FROM v$session where serial# = '11016'；
杀掉这个进程
alter system kill session '72,11016'；
批量解锁
declare cursor mycur is
select b.sid,b.serial# from v$locked_object a,v$session b where a.session_id = b.sid group by b.sid,b.serial#;
begin
for cur in mycur
loop
execute immediate ( 'alter system kill session '''||cur.sid || ','|| cur.SERIAL# ||''' ');
end loop;
end;
查看表空间及使用情况
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 ;
Oracle查看表空间使用和增加表空间文件：
select * From dba_data_files t where t.TABLESPACE_NAME = 'DT_IMCP_DAT';
select a.tablespace_name,
a.bytes / 1024 / 1024 "sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
alter tablespace DT_IMCP_DAT add datafile '/home/oracle/app/oradata/orclDT_IMCP_DAT11.ORA' size 3200M；
Oracle如何定时删除有分区的表数据
create or replace procedure delPart as
table_owner varchar2(50); --存储拥有者
table_name varchar2(100); --存储表名
table_subobject_name varchar2(100); --存储分区表名
v_SqlExec varchar2(200); --存储拼接后的语句
cursor fetch_cursor is select OWNER,OBJECT_NAME,SUBOBJECT_NAME,CREATED
from dba_objects where owner='SYS'
and object_type ='TABLE PARTITION'
and GENERATED ='Y' --创建表时候创建的第一个分区（GENERATED ='N'）是不允许被删除
and object_name in('TEST_PART')
and created<=TRUNC(SYSDATE+3);
begin
for get_cursor in fetch_cursor loop
table_owner:=get_cursor.OWNER;
table_name:=get_cursor.OBJECT_NAME;
table_subobject_name:=get_cursor.SUBOBJECT_NAME;
--拼接删除分区语句
v_SqlExec:='alter table '|| table_owner ||'.'||table_name||' DROP PARTITION '||table_subobject_name;
--打印语句
DBMS_OUTPUT.PUT_LINE('删除分区'||v_SqlExec);
--执行语句
execute immediate v_SqlExec;
end loop;
end;
Oracle导出库表空表处理
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;
再将查询的结果语句执行即可
Oracle之ORA-01940：无法删除当前已连接用户的解决方案
1.首先将索要删除的用户锁定，这句必须执行，否则之后杀死进程无效！
alter user 用户名 account lock;
2.从【v$Session】表查看当前用户占用资源，有使用资源的情况下，肯定不能删除用户
select saddr,sid,serial#,paddr,username,status from v$session where username = '用户名';
3. 杀死status为【 INACTIVE】的进程，sid和seria#值为该列下的数值
alter system kill session 'sid,serial#';
4. 删除用户，如果不成功，即还是会报01940错，因为还有【 INACTIVE】进程没杀死
drop user 用户名 cascade;
Oracle创建定时任务执行存储过程（带参数）
存储过程：
create or replace procedure table_drop_partition(table_owner varchar2,table_name varchar2,keep_days in number) as
--table_owner varchar2(50); --存储拥有者
-- table_name varchar2(100); --存储表名
table_subobject_name varchar2(100); --存储分区表名
v_SqlExec varchar2(200); --存储拼接后的语句
cursor fetch_cursor is select owner,object_name,subobject_name,created
from dba_objects
where owner=upper(table_owner)
and object_type ='TABLE PARTITION'
and GENERATED ='Y' --创建表时候创建的第一个分区（GENERATED ='N'）是不允许被删除
and object_name in( upper(table_name) )
and created<=TRUNC( SYSDATE + keep_days );
--v_seg fetch_cursor%rowtype;
begin
for get_cursor in fetch_cursor loop
--table_owner:=get_cursor.OWNER;
--table_name:=get_cursor.OBJECT_NAME;
table_subobject_name:=get_cursor.SUBOBJECT_NAME;
--拼接删除分区语句
v_SqlExec:='alter table '|| table_owner ||'.'||table_name||' DROP PARTITION '||table_subobject_name;
--打印语句
DBMS_OUTPUT.PUT_LINE('删除分区'||v_SqlExec);
--执行语句
execute immediate v_SqlExec;
end loop;
end;
定时任务：
--ZHDD是当前登录账号
--DROP_FCD_RAW_PARTITION定时任务名，自定义
--table_drop_partition存储过程名
--number_of_arguments => 3 传参数量
--start_date => to_date('20-04-2023 21:14:57', 'dd-mm-yyyy hh24:mi:ss') 任务开始执行时间
--repeat_interval => 'Freq=MINUTELY;Interval=1' 时间间隔自定义
--argument_position => 1 参数索引，表示第几个参数
--argument_value => 'ZHDD' 参数对应的值
begin
sys.dbms_scheduler.create_job(job_name => 'ZHDD.DROP_FCD_RAW_PARTITION',
job_type => 'STORED_PROCEDURE',
job_action => 'table_drop_partition',
number_of_arguments => 3,
start_date => to_date('20-04-2023 21:14:57', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=MINUTELY;Interval=1',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => true,
comments => '');
sys.dbms_scheduler.set_job_argument_value(job_name => 'ZHDD.DROP_FCD_RAW_PARTITION',
argument_position => 1,
argument_value => 'ZHDD');
sys.dbms_scheduler.set_job_argument_value(job_name => 'ZHDD.DROP_FCD_RAW_PARTITION',
argument_position => 2,
argument_value => 'TEST_PART_RAW');
sys.dbms_scheduler.set_job_argument_value(job_name => 'ZHDD.DROP_FCD_RAW_PARTITION',
argument_position => 3,
argument_value => '5');
sys.dbms_scheduler.enable(name => 'ZHDD.DROP_FCD_RAW_PARTITION');
end;
/
执行结果查询
select * from dba_scheduler_job_log where job_name='DROP_FCD_RAW_PARTITION';
浙公网安备 33010602011771号