1、查看表空间使用率
    set line 220
    select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB total.MB) * 100, 2) || ‘%’ as Used_Pct
    from (select tablespace_name,sum(bytes) 1024 1024 as MB from dba_free_space group by tablespace_name) free,
    (select tablespace_name,sum(bytes) 1024 1024 as MB from dba_data_files group by tablespace_name) total
    where free.tablespace_name = total.tablespace_name order by used_pct desc;

    2、查询单个表空间使用率
    select total.tablespace_name,
    round(total.MB, 2) as Total_MB,
    round(total.MB - free.MB, 2) as Used_MB,
    round((1 - free.MB total.MB) * 100, 2) || ‘%’ as Used_Pct from
    (select tablespace_name,sum(bytes) 1024 1024 as MB from dba_free_space where tablespace_name=’TBL_SPACE’ group by tablespace_name) free,
    (select tablespace_name,sum(bytes) 1024 1024 as MB from dba_data_files where tablespace_name=’TBL_SPACE’ group by tablespace_name) total
    where free.tablespace_name = total.tablespace_name order by used_pct desc;

    3、查看临时表空间数据文件位置，大小，及是否自动扩展
    select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from dba_data_files where tablespace_name in (‘’) order by tablespace_name;
    select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

    —查看所有临时表空间大小
    SELECT D.TABLESPACE_NAME,SPACE “SUM_SPACE(M)”,BLOCKS SUM_BLOCKS,
    USED_SPACE “USED_SPACE(M)”,ROUND(NVL(USED_SPACE,0)/SPACE100,2) “USED_RATE(%)”,
    NVL(FREE_SPACE,0) “FREE_SPACE(M)”
    FROM
    (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024    1024),2) SPACE,SUM(BLOCKS) BLOCKS
    FROM DBA_TEMP_FILES
    GROUP BY TABLESPACE_NAME) D,
    (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(10241024),2) USED_SPACE,
    ROUND(SUM(BYTES_FREE)/(1024    1024),2) FREE_SPACE
    FROM V$TEMP_SPACE_HEADER
    GROUP BY TABLESPACE_NAME) F
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

    4、查看ASM磁盘空间
    select name,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;

    5、查询oracle的连接数
    select count(*) from v$session;

    6、查看不同用户的连接数
    select username,count(username) from v$session where username is not null group by username;

    7、查看回收站
    show recyclebin

    8、清空回收站
    PURGE recyclebin

    9、删除表，但不进入回收站
    drop table tableName purge;

    10、查询用户下所有创建表的语句
    select
    ‘select dbms_metadata.get_ddl(‘||’’’’||’TABLE’||’’’’||’,’||’’’’||table_name||’’’’||’) from dual;’||chr(10)||’select ‘||’’’’||’/‘||’’’’|| ‘ from dual;’
    from user_tables;

    11、查询当时创建用户的语句
    select dbms_metadata.get_ddl(‘USER’,’USERNAME’) from dual;

    12、查询普通用户语句
    select username from dba_users where account_status=’OPEN’;

    13、修改数据文件大小
    alter database datafile ‘&path_name’ resize 10G;
    alter database datafile &{file_id} resize 10G;

    14、添加数据文件
    alter tablespace &tablespace_name ADD datafile ‘&datafile_name’ SIZE xxx;

    15、临时表空间扩容、添加临时表空间数据文件
    ALTER TABLESPACE &tablespace_name ADD TEMPFILE ‘&datafile_name’ SIZE xxx;
    15.大文件表空间扩容
    ALTER TABLESPACE &tablespace_name RESIZE xxx;

    16、大文件表空间扩容
    ALTER TABLESPACE &tablespace_name RESIZE xxx

    17、查询告警日志文件位置
    show parameter dump
    select * from v$diag_info

    18、创建用户
    create user username identified by password default tablespace dbdbs;

    19、创建组
    groupadd -g 1000 oinstall

    20、赋权
    grant dba to user

    21、查出锁的会话
    select b.username,b.sid,b.serial#,logon_time from v$lock_object a,v$session b where a.session_id = b.sid order by b.logon_time

    22、查询数据库中所有用户下占用物理空间内存大小
    select owner,sum(bytes)/1024/1024 MB from dba_segments group by owner;

    23、日志切换
    alter system switch logfile;

    24、查看归档是否开启
    archive log list;
    select log_mode from v$database;

    25、开启归档
    alter system set log_archive_dest_1=’LOCATION=+ARCH’ scope=both sid=’*’;
    shu immediate
    startup mount
    alter database archivelog
    alter database open

    26、监听注册
    alter system set LOCAL_LISTENER=’(ADDRESS = (PROTOCOL = TCP)(HOST = JiekeXu)(PORT = 1522))’;
    alter system set LOCAL_LISTENER=’(ADDRESS = (PROTOCOL = TCP)(HOST = 10.X.X.6)(PORT = 1521))’ SID=’JiekeDBR2’ scope=both;

    27、数据库注册监听
    alter system register;

    28、创建DBLINK
    create public database link HO
    connect to SKDATA identified by oracle
    using ‘(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.X.X.6)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = JiekeXuDB)
    )
    )’;

    29、删除DBLINK
    drop public database link &dblinkname;

    30、查询DBLINK
    select * from dba_db_links

    31、查询本某个地用户登陆的进程
    ps -ef|grep LOCAL=NO

    32、批量杀进程执行 ，注意检查oracle_sid
    ps -ef|grep $ORACLESID|grep -v ora|grep LOCAL=NO|awk ‘{print $2}’|xargs kill -9

    33、查询系统检查点SCN的命令
    select CHECKPOINT_CHANGE# from v$database;

    34、查询控制文件中数据文件的SCN命令
    select name,file#,checkpoint_change# from v$datafile;

    35、控制文件中的数据文件终止scn
    select name,last_change# from v$datafile;

    36.解锁用户命令
    alter user 用户名 account unlock; ——这个只会解锁账号，而不会取消密码过期。

    37.设置密码无期限
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 将概要文件(如default)的密码有效期由默认的180天修改成“无限制”（修改之后不需要重启动数据库，会立即生效）

    38.查询字符集命令

    select userenv (‘language’) from dual;

    39.查询数据库里某个表空间里所占用的大小：
    select OWNER,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 as MB from dba_segments where TABLESPACE_NAME=’AT01’ order by 4 desc;

    40关于DG认清主备库。
    select d.NAME,d.LOG_MODE,d.OPEN_MODE,d.PROTECTION_MODE,d.DATABASE_ROLE from v$database d;
    名字，日志模式，开启模式，保护模式，数据库角色。其中数据库角色就是看主备库的.
    PRIMARY为主库 PHYSICAL STANDBY物理备库,LOGICAL STANDBY逻辑备库.
    在备库中查询：
    show parameter fal_server
    得出的值在与tns中找相匹配的server_name然后在寻找对应的IP地址。
    开启恢复
    SQL> recover managed standby database disconnect from session using current logfile;
    ADG相关命令：
    select name,db_unique_name,database_role,protection_mode,open_mode from v$database; —查看数据库状态
    select process,status,sequence# from v$managed_standby;—查看相关进程
    recover managed standby database cancel; —关闭mrp功能
    recover managed standby database disconnect from session using current logfile;—开启当前日志应用
    alter database recover managed standby database disconnect from session;—-开启不同步当前日志应用
    select dest_id,error,status,log_sequence,applied_scn from v$archive_dest;—查看归档路径状态
    SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;—-查询传输至备库的日志是否应用。

    41.添加ASM磁盘所需命令
    在AIX系统中查询新加入的盘符 lspv
    在AIX系统中查询盘空间大小：getconf DISK_SIZE /dev/hdisk1
    在数据库中查询ASM的中是否使用了此硬盘：col PATH for a44
    select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE from v$asm_disk;

    42.使用轻松连接模式远程登录数据库
    sqlplus sjglt/sjglt@1X.1XX.1X6.16:1521/orcl

    43.更改备份保存时间
    alter system set control_file_record_keep_time=39;

    45.查询oracle连接最大值命令：
    select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in (‘processes’,’sessions’);
    MAX_UTILIZATION代表数据库自上次启动以来达到的最大值，LIMIT_VALUE代表设置的最大值。

    46.查询锁SQL:
    select ‘blocker(‘||lb.sid||’:’||sb.username||’)-sql:’|| qb.sql_text blockers,
    ‘waiter (‘||lw.sid||’:’||sw.username||’)-sql:’|| qw.sql_text
    waiters
    from v$lock lb,
    v$lock lw,
    v$session sb,
    v$session sw,
    v$sql qb,
    v$sql qw
    where lb.sid=sb.sid
    and lw.sid=sw.sid
    and sb.prev_sql_addr=qb.address
    and sw.sql_address=qw.address
    and lb.id1=lw.id1
    and sw.lockwait is not null
    and sb.lockwait is null
    and lb.block=1 ;

    47.查询数据库数据量
    select sum（bytes）/1024/1024/1024 total_gb from dba_segment

    48.查看sql的执行计划
    select * from v$sql_plan where sql_id=’&sql_id’;

    50.查看绑定变量
    select * from v$sql_bind_capture sbc where sql_id=’&sql_id’;

    51.关于rman归档问题
    rman> crosscheck archivelog all;
    rman> delete noprompt expired archivelog all;
    rman> delete force noprompt archivelog until time ‘sysdate - 30’;
    单节点：
    delete noprompt archivelog until sequence ${Seq};
    rac（thread后面加节点数）:
    delete noprompt archivelog until sequence 1 thread ${CurThread};
    删除7天前的归档：
    DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7’;
    按时间删除归档：
    delete force archivelog all completed before ‘SYSDATA-1/24’;
    查看归档每天切换频率以及大小
    select max (first_time) max_first_time,
    to_char (first_time, ‘yyyy-mm-dd’) day,
    count (recid) count_number,
    count (recid) 200 size_mb
    from v$log_history
    group by to_char (first_time, ‘yyyy-mm-dd’)
    order by 1;
    按天计算日志生成量
    set pages 9999
    select to_char(first_time,’yyyymmdd’),count(    ) from v$log_history group by to_char(first_time,’yyyymmdd’) order by 1;

    52.查看数据库引起锁表的SQL语句：
    SELECT A.USERNAME,
    A.MACHINE,
    A.PROGRAM,
    A.SID,
    A.SERIAL#,
    A.STATUS,
    C.PIECE,
    C.SQL_TEXT
    FROM V$SESSION A,
    V$SQLTEXT C
    WHERE A.SID IN (SELECT DISTINCT T2.SID
    FROM V$LOCKED_OBJECT T1,
    V$SESSION T2
    WHERE T1.SESSION_ID = T2.SID)
    AND A.SQL_ADDRESS = C.ADDRESS(+)
    ORDER BY C.PIECE;

    53.查询DB负载情况
    SELECT *
    FROM ( SELECT A.INSTANCE_NUMBER,
    A.SNAP_ID,
    B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
    B.END_INTERVAL_TIME + 0 END_TIME,
    ROUND(VALUE - LAG( VALUE, 1 , ‘0’)
    OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) “DB TIME”
    FROM (SELECT B.SNAP_ID,
    INSTANCE_NUMBER,
    SUM(VALUE ) / 1000000 / 60 VALUE
    FROM DBA_HIST_SYS_TIME_MODEL B
    WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
    AND UPPER (B.STAT_NAME) IN UPPER((‘DB TIME’ ))
    GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
    DBA_HIST_SNAPSHOT B
    WHERE A.SNAP_ID = B.SNAP_ID
    AND B.DBID = (SELECT DBID FROM V$DATABASE)
    AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
    WHERE TO_CHAR(BEGIN_TIME, ‘YYYY-MM-DD’) = TO_CHAR(SYSDATE , ‘YYYY-MM-DD’)
    ORDER BY BEGIN_TIME;

    54.杀应用连接
    ps -ef |grep xxx
    kill -9 pid

    55.查询某个时间点的scn
    select timestamp_to_scn(to_timestamp(‘2021-01-06 10:00:00’,’yyyy-mm-dd hh24:mi:ss’)) from dual;

    56.闪回到某个scn点：（闪回前请先进行备份）
    alter table tb_flash1 enable row movement
    flashback table tb_flash1 to 115398523;

    57.查询数据库最近一次重启时间
    alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
    select startup_time from v$instance;

    58.查看日志每小时文件切换频率
    column h0 format 999
    column h1 format 999
    column h2 format 999
    column h3 format 999
    column h4 format 999
    column h5 format 999
    column h6 format 999
    column h7 format 999
    column h8 format 999
    column h9 format 999
    column avg format 999.99
    column day format a6
    SELECT TRUNC (first_time) “Date”, TO_CHAR (first_time, ‘Dy’) “Day”, COUNT (1) “Total”,
    SUM (DECODE (TO_CHAR (first_time, ‘hh24’), ‘00’, 1, 0)) h0,
    SUM (DECODE (TO_CHAR (first_time, ‘hh24’), ‘01’, 1, 0)) “h1”,
    SUM (DECODE (TO_CHAR (first_time, ‘hh24’), ‘02’, 1, 0)) “h2”,
    SUM (DECODE (TO_CHAR (first_time, ‘hh24’), ‘03’, 1, 0)) “h3”,
    to_char(ROUND (COUNT (1) / 24, 2),’fm99999999990.00’) “Avg”
    FROM gv$log_history
    WHERE first_time >= trunc(SYSDATE) - 30
    and thread# = inst_id
    GROUP BY TRUNC (first_time), TO_CHAR (first_time, ‘Dy’)
    ORDER BY 1 DESC;

    59.更改会话时间显示格式
    alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;

    60.查阻塞
    col username for a15
    col program for a30
    col event for a30
    select sid,serial#,username,blocking_session,last_call_et,prev_sql_id,final_blocking_session,event,machine,program,WAIT_TIME
    from gv$session where blocking_session is not null;

    61.查看表的统计信息
    select
    OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,ROW_MOVEMENT,PARTITIONED,GLOBAL_STATS,
    USER_STATS,to_char(LAST_ANALYZED,’YYYY-MM-DD’) from dba_tables where table_name=’&1’;

    62.根据PID查登陆用户及时间
    select s.SID,s.SERIAL#,s.LOGON_TIME,s.OSUSER,s.USERNAME,s.STATUS,s.PROGRAM from v$session s where s.SID in(
    select sid from v$session where paddr in (select addr from v$process where spid in (
    sipd1,
    spid2,
    spid3,
    )

    63，查看表空间属性
    SELECT * FROM dba_tablespace wheretablespace_name = ‘表空间名’;

    64,修改表空间状态、名字
    alter tablespace 表空间名 表空间状态;
    alter tablespace 表空间名 1 rename to 表空间名 2;

    65,修改表空间对应的数据文件大小
    alter database
    datafile ‘表空间文件路径名称’
    resize 大小

    66、查询堵塞别的会话超过30分钟且自身是不活动的会话
    select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session where sid in (select FINAL_BLOCKING_SESSION from v$session
    where state=’WAITING’ and BLOCKING_SESSION_STATUS=’VALID’ and FINAL_BLOCKING_SESSION_STATUS=’VALID’) and status=’INACTIVE’
    and sql_id is null and seconds_in_wait>1800;

    67、查询正在执行的 dbms_job
    select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process
    where a.sid=b.sid and paddr=addr;

    68、查询当前正在消耗临时空间的 SQL 语句
    Select distinct se.username,se.sid,
    su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,
    su.tablespace,sql_text
    from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s
    where p.name = ‘db_block_size’ and su.session_addr=se.saddr
    and su.sqlhash=s.hash_value and su.sqladdr=s.address and se.STATUS=’ACTIVE’;

    69、统计每个用户使用表空间率
    SELECT c.owner “用户”,
    a.tablespace_name “表空间名”,
    total/1024/1024 “表空间大小M”,
    free/1024/1024 “表空间剩余大小M”,
    ( total - free )/1024/1024 “表空间使用大小M”,
    Round(( total - free ) / total, 4) 100 “表空间总计使用率 %”,
    c.schemas_use/1024/1024 “用户使用表空间大小M”,
    round((schemas_use)/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,
    (Select owner ,Tablespace_Name,
    Sum(bytes) schemas_use
    From Dba_Segments
    Group By owner,Tablespace_Name) c
    WHERE a.tablespace_name = b.tablespace_name
    and a.tablespace_name =c.Tablespace_Name
    order by “用户”,”表空间名”;