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';

posted @ 2023-02-14 15:41  #码农9527#  阅读(45)  评论(0)    收藏  举报