1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# 监听管理
lsnrctl status
lsnrctl start
lsnrctl stop

# 管理员登陆
sqlplus / as sysdba

# 起停库
SQL> shutdown immediate;
SQL> startup

# sqlplus login
sqlplus wait/passw0rd
sqlplus wait/passw0rd@10.2.1.65/orcl
sqlplus 'user1/"passwd"'@10.1.249.45/sso_test    # 特殊密码字符密码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

desc table_name;                -- 查看表 DDL 语句

-- 删除指定的表
drop table t1;
drop table t1 purge;

truncate table table_name;

--- 查询表大小
select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='t_table';
select sum(round(BYTES/1024/1024,2)) from user_segments where segment_name='t_table';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

--- 查询表空间的使用率(集合查询)
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) "percent_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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

--- 查看表锁
SELECT l.session_id sid,
       s.serial#,
       l.locked_mode 锁模式,
       l.oracle_username 登录用户,
       l.os_user_name 登录机器用户名,
       s.machine 机器名,
       s.terminal 终端用户名,
       o.object_name 被锁对象名,
       s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
ORDER BY sid, s.serial#;

--- 查看详细的锁信息
SELECT l.session_id sid,
       s.serial#,
       l.locked_mode 锁模式,
       l.oracle_username 登录用户,
       l.os_user_name 登录机器用户名,
       s.machine 机器名,
       s.terminal 终端用户名,
       o.object_name 被锁对象名,
       o.object_type 被锁对象类型,
       s.logon_time 登录数据库时间,
       a.sql_text 当前执行的语句
FROM v$locked_object l, all_objects o, v$session s,v$sqlarea a
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
   AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;

1
2
3
4
5

-- oracle dg
SELECT substr((SUBSTR(VALUE,5)),0,2)*3600 + substr((SUBSTR(VALUE,5)),4,2)*60 + substr((SUBSTR(VALUE,5)),7,2) AS seconds, VALUE
FROM v$dataguard_stats a
WHERE NAME ='apply lag'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- 看数据库链接数
select count(*) from v$process;

--- 并发连接数
select count(*) from v$session where status='ACTIVE';

-- 查看当前数据库建立的会话情况
select count(*) from v$session;
select sid,serial#,username,program,machine,status from v$session;
select count(*) from v$session where username not in('SYS','SYSTEM') and username is not null and STATUS='ACTIVE';
    -- 活跃普通用户

select name,value from v$parameter
select name,value value from v$sysstat

-- 查看对象的创建时间
SELECT CREATED FROM USER_OBJECTS WHERE OBJECT_NAME='TABLE';

show parameter pga_aggregate_target                 -- 看 PGA 大小
show parameter sga_%                                -- 看 SAG 大小
show parameter processes;                           -- 查看当前连接数设置
select userenv('language') from dual;               -- 查询字符集
select tablespace_name from dba_tablespaces;        -- 查询已有的表空间
select * from dba_directories;                      -- 查看数据库目录变量

select * from dba_users;                            -- 列出系统中所有用户
select table_name from user_tables;                 -- 查看当前用户在数据库下的表
select instance_name from v$instance;               -- 查看实例名


select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;
--- 查询用户表、索引、分区表所占用的空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;
--- 用户表空间占用

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;
--- 分区表空间占用

--- 查询定时作业任务的执行情况
select job,log_user,last_date,failures from dba_jobs;

--- 查询数据文件的有效性
select file_name,status from dba_data_files;

1
2
3
4
5

select
    c.ip,
    -- 求某个值出现次数的一种方法
    sum(case when c.result='0' then '1' else '0' end) login_sum
from xxxx c;