oracle常用SQL语句

获取表

1
2
3
4
select table_name from user_tables; //当前用户的表      
select table_name from all_tables; //所有用户的表  
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner='username'

获取表字段

1
2
3
4
select *
  from user_tab_columns
 where Table_Name = 'table_name'
 order by column_name

剩余表空间百分比

1
2
3
4
5
6
7
8
9
10
11
12
13
select df.tablespace_name "表空间名",
         totalspace "总空间M",
         freespace "剩余空间M",
         round((1 - freespace / totalspace) * 100, 2) "使用率%"
          from (select tablespace_name,
                 round(sum(bytes) / 1024 / 1024) totalspace
            from dba_data_files
           group by tablespace_name) df,
         (select tablespace_name,
                 round(sum(bytes) / 1024 / 1024) freespace
            from dba_free_space
           group by tablespace_name) fs
         where df.tablespace_name = fs.tablespace_name;

检查依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Select b.table_name  主键表名,
       b.column_name 主键列名,
       a.table_name  外键表名,
       a.column_name 外键列名
  From (Select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.constraint_name = b.constraint_name) a,
       (Select Distinct a.r_constraint_name, b.table_name, b.column_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.r_constraint_name = b.constraint_name) b
 Where a.r_constraint_name = b.r_constraint_name

检查被锁定的表

1
2
3
4
select object_name, machine, s.sid, s.serial#
  from v$locked_object l, dba_objects o, v$session s
 where l.object_id = o.object_id
   and l.session_id = s.sid;

计算表占用空间的大小

1
2
3
4
5
6
7
select segment_name table_name,
       sum(blocks) blocks,
       sum(bytes) / (1024 * 1024) "table_size[mb]"
  from user_segments
 where segment_type = 'table'
   and segment_name = &table_name
 group by segment_name;

查看数据库是否为CDB

1
2
3
4
5
6
7
8
select name,
       decode(cdb,
              'YES',
              'Multitenant Option enabled',
              'Regular 12c Database: ') "Multitenant Option",
       open_mode,
       con_id
  from v$database;

mac电脑上获取当前WiFi的密码

安装

1
brew install wifi-password

使用

1
wifi-password

#测试

1
2
3
4
5
6
7
8
9
10
11
12
13
1$brew install wifi-password
Warning: You are using OS X 10.12.
We do not provide support for this pre-release version.
You may encounter build failures or other breakages.
==> Downloading https://github.com/rauchg/wifi-password/archive/0.1.0.tar.gz
==> Downloading from https://codeload.github.com/rauchg/wifi-password/tar.gz/0.1
######################################################################## 100.0%
🍺  /usr/local/Cellar/wifi-password/0.1.0: 4 files, 3.4K, built in 6 seconds
2$wifi-password
 … getting password for "WXIIVY".
 … keychain prompt incoming.
✓ "ITKF1234"