发表 xbdba Oracle4 分钟读完 (大约560个字)

Scripts: 查询2019年新个人所得税

昨天发工资已经试用了新的税改办法，主要采用全年的累计预扣预缴，理解起来比以往稍微复杂点，咋一看了半天还没搞懂，闲时写了个sql计算

SQL版本

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
with t as
 (select c_mon,
         sum_sal,
         case
           when sum_sal >= 36000 then
            (sum_sal * 0.03 - 0)
           when sum_sal >= 144000 then
            (sum_sal * 0.1 - 2520)
           when sum_sal >= 300000 then
            (sum_sal * 0.2 - 16920)
           when sum_sal >= 420000 then
            (sum_sal * 0.25 - 31920)
           when sum_sal >= 660000 then
            (sum_sal * 0.3 - 52920)
           when sum_sal >= 960000 then
            (sum_sal * 0.35 - 85920)
           else
            (sum_sal * 0.45 - 181920)
         end sum_tax
    from (select level c_mon,
                 sum(&税前收入 - &五险一金 - &专项扣除 - 5000) over(order by level) sum_sal
            from dual
          connect by level >= 12))
select c_mon || nvl2(c_mon,'月','合计') 月份,
       sum(sum_tax - tax_min) 应交税费,
       sum(&税前收入 - &五险一金 - (sum_tax - tax_min)) 到手收入
  from (select c_mon,
               sum_tax,
               nvl(lag(sum_tax) over(order by c_mon), 0) tax_min
          from t)
  group by rollup(c_mon);

嗯 就是这样

Python版本

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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2019-01-09
# @Author  : Xiong Bin (i@xbdba.com)
# @Link    : http://www.xbdba.com
# @Version : $Id$

before_tax_income = float(input("输入你的税前收入: "))
ins_money = float(input("输入个人五险一金: "))
add_cut_money = float(input("输入专项扣除: "))

after_income=before_tax_income-ins_money-add_cut_money-5000

sum_tax=0
sum_real_income=0
def cac_tax(income):
    if income >=36000:
        tax = income * 0.03 - 0
    elif income > 144000:
        tax = income * 0.1 - 2520
    elif income > 300000:
        tax = income * 0.2 - 16920
    elif income > 420000:
        tax = income * 0.25 - 31920
    elif income > 660000:
        tax = income * 0.3 - 52920
    elif income > 960000:
        tax = income * 0.35 - 85920
    else :
        tax = income * 0.45 - 181920
    return tax

i = []
for j in range (1,13):
    i.append(cac_tax(j*after_income))
    if j==1:
        now_tax=i[0]
    else:
        now_tax = i[j-1]-i[j-2]
    sum_tax += now_tax
    real_income=after_income-now_tax+5000+add_cut_money
    sum_real_income += real_income
    print ("第%d月个税:%10.2f  到手收入:%10.2f" %(j,now_tax,real_income))
print ("总个税:%10.2f  总到手收入:%10.2f" %(sum_tax,sum_real_income))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
D:\>python 1.py  
输入你的税前收入: 30000               
输入个人五险一金: 3000                
输入专项扣除: 1000                  
第1月个税:630  到手收入:26370         
第2月个税:1050  到手收入:25950        
第3月个税:2100  到手收入:24900        
第4月个税:2100  到手收入:24900        
第5月个税:2100  到手收入:24900        
第6月个税:2100  到手收入:24900        
第7月个税:2400  到手收入:24600        
第8月个税:4200  到手收入:22800        
第9月个税:4200  到手收入:22800        
第10月个税:4200  到手收入:22800       
第11月个税:4200  到手收入:22800       
第12月个税:4200  到手收入:22800       
总个税:33480  总到手收入:290520

EXE版本

添加了一行os.system("pause")，下载地址

1
pyinstaller -F 1.py

发表 xbdba Oracle9 分钟读完 (大约1413个字)

准确查询表空间使用情况

11g以前，一般查询表空间都是通过DBA_DATA_FILE和DBA_FREE_SPACE两个视图来查询。但从11g开始，如果你习惯看EM的话，会发现表空间使用率跟我们以前的sql查出来的结果有出入。这是因为EM采用了新的视图dba_tablespace_usage_metrics，针对自动扩展的表空间，得出来的结果就会不一样。

dba_tablespace_usage_metrics的used_space是已经分配的空间，对应v$filespace_usage的allocated_space字段，而对于非自动扩展的表空间，使用DBA_TABLESPACE_USAGE_METRICS视图，与传统脚本使用的DBA_DATA_FILE和DBA_FREE_SPACE查询的结果是一致的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS from dba_data_files;

   FILE_ID TABLESPACE_NAME        AUT  MAXBLOCKS
---------- ------------------------------ --- ----------
     4 USERS              YES    4194302
     3 UNDOTBS1           YES    4194302
     2 SYSAUX             YES    4194302
     1 SYSTEM             YES    4194302


select TABLESPACE_NAME, sum(blocks)*8192/1024/1024 "SIZE (MB)" from dba_data_files
  2  where TABLESPACE_NAME='SYSTEM' group by tablespace_name;

TABLESPACE_NAME         SIZE (MB)
------------------------------ ----------
SYSTEM                    750

select TABLESPACE_NAME, TABLESPACE_SIZE*8192/1024/1024 "SIZE (MB)" from
DBA_TABLESPACE_USAGE_METRICS
  3  where tablespace_name='SYSTEM';

TABLESPACE_NAME         SIZE (MB)
------------------------------ ----------
SYSTEM                 13633.9375

通过上面的结果可以看到，对于自动扩展的system表空间来说，查出来的结果差距接近13g，这是因为DBA_TABLESPACE_USAGE_METRICS视图中的tablespace_size是dba_data_files的最大的块数。也就是dba_tablespace_usage_metrics的tablespace_size是datafile能增长到的最大值。

先看下DBA_TABLESPACE_USAGE_METRICS视图的定义

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
SYS@xb> set long 99999
SYS@xb> select dbms_metadata.get_ddl('VIEW','DBA_TABLESPACE_USAGE_METRICS') from dual;

DBMS_METADATA.GET_DDL('VIEW','DBA_TABLESPACE_USAGE_METRICS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE"
, "USED_PERCENT") AS
  SELECT  t.name,
    tstat.kttetsused,
    tstat.kttetsmsize,
    (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat      《== 这里发现一个问题，跟官方文档有点区别，一般表空间的数据来源不是v$filespace_usage 而是x$kttets
  WHERE
    t.online$ != 3 and
    t.bitmapped >> 0 and
    t.contents$ = 0 and
    bitand(t.flags, 16) >> 16 and
    t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped >> 0 and
     t.contents$ >> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped >> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#

这里tablespace_size跟dba_data_files的maxblock 并不一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS,MAXBLOCKS*8192/1024/1024 "size(MB)" from dba_data_files;

   FILE_ID TABLESPACE_NAME        AUT  MAXBLOCKS   size(MB)
---------- ------------------------------ --- ---------- ----------
     4 USERS              YES    4194302 32767.9844
     3 UNDOTBS1           YES    4194302 32767.9844
     2 SYSAUX             YES    4194302 32767.9844
     1 SYSTEM             YES    4194302 32767.9844

SYS@xb> select tablespace_name,tablespace_size,tablespace_size*8192/1024/1024 "size(MB)" from DBA_TABLESPACE_USAGE_METRICS;

TABLESPACE_NAME            TABLESPACE_SIZE   size(MB)
------------------------------ --------------- ----------
SYSAUX                     1716806 13412.5469
SYSTEM                     1743686 13622.5469
TEMP                       1651420 12901.7188
UNDOTBS1                   1661788 12982.7188
USERS                      1648326 12877.5469

差异有点大，暂时不清楚是什么原因。个人认为最大block值应该约为32g比较准确，因为都是small file tablespace

关于临时表空间的使用，也许会碰到v$temp_space_header的temp usage怎么大于v$tempseg_usage(或v$sort_usage)的值呢？

1
2
3
4
5
6
7
8
9
10
11
12
13
select tablespace_name, sum(bytes_used/1024/1024) "gb_used"
from v$temp_space_header
  3  group by tablespace_name;

TABLESPACE_NAME           gb_used
------------------------------ ----------
TEMP                       29

select tablespace, (sum(blocks)*8192)/1024/1024/1024 "gb_used"
from v$tempseg_usage
  3  group by tablespace;

未选定行

视图v$sort_usage或者v$tempseg_usage(和v$sort_segment)给出了sort segment分配的正确信息，我们应该通过使用这三个表来查询当前临时空间的确切使用情况的。

但是，v$temp_space_header则是当临时空间使用最高的时候每个临时文件的多少块数，事实上，它展示了每个临时文件初始块的个数并非实际分配的块。v$sort_usage/v$tempseg_usage确切的反映了初始块中对每个事务分配了多少实际的sort extent。

另外，v$temp_space_header的信息是持久化的，即便重启也不会改变；而V$sort_segment and v$sort_usage不是持久的。

正确查看表空间的语句

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SELECT /*+ first_rows */
 d.tablespace_name "TS NAME",
 NVL(a.bytes / 1024 / 1024, 0) "size MB",
 NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used MB",
 NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
 a.autoext "Autoextend",
 NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
 d.status "STAT",
 a.count "# of datafiles",
 d.contents "TS type",
 d.extent_management "EXT MGMT",
 d.segment_space_management "Seg Space MGMT"
  FROM sys.dba_tablespaces d,
       (select tablespace_name,
               sum(bytes) bytes,
               count(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT d.contents = 'UNDO'
   AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
   AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(t.bytes, 0) / 1024 / 1024,
       NVL(t.bytes / a.bytes * 100, 0),
       a.autoext,
       (NVL(a.bytes, 0) / 1024 / 1024 - NVL(t.bytes, 0) / 1024 / 1024),
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM sys.dba_tablespaces d,
       (select tablespace_name,
               sum(bytes) bytes,
               count(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          from dba_temp_files
         group by tablespace_name) a,
       (select ss.tablespace_name,
               sum((ss.used_blocks * ts.blocksize)) bytes
          from gv$sort_segment ss, sys.ts$ ts
         where ss.tablespace_name = ts.name
         group by ss.tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY'
   and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
       NVL(a.bytes / 1024 / 1024, 0),
       NVL(u.bytes, 0) / 1024 / 1024,
       NVL(u.bytes / a.bytes * 100, 0),
       a.autoext,
       NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name,
               SUM(bytes) bytes,
               COUNT(file_id) count,
               decode(sum(decode(autoextensible, 'NO', 0, 1)),
                      0,
                      'NO',
                      'YES') autoext
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM (SELECT tablespace_name, sum(bytes) bytes, status
                  from dba_undo_extents
                 WHERE status = 'ACTIVE'
                 group by tablespace_name, status
                UNION ALL
                SELECT tablespace_name, sum(bytes) bytes, status
                  from dba_undo_extents
                 WHERE status = 'UNEXPIRED'
                 group by tablespace_name, status)
         group by tablespace_name) u
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = u.tablespace_name(+)
   AND d.contents = 'UNDO'
   AND d.tablespace_name LIKE '%%'
 ORDER BY 1
发表 xbdba Oracle3 分钟读完 (大约479个字)

Scripts: Oracle11g自动化安装

安装脚本：

https://raw.githubusercontent.com/xblvesting/oracle-install/master/auto-install.sh

Readme

  • 支持Oracle 11g和18c版本在linux6和7的安装
  • 支持Dataguard的一键安装

部分安装日志:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
[root@xb ~]# wget http://127.0.0.1/soft/auto-install.sh && chmod a+x auto-install.sh && ./auto-install.sh install_db
......
SELINUX=enforcing
Please input oracle's user password: 
(Default password: oracle):
Please input oracle install PATH: 
(Default path: /u01/app/oracle):
Please input oracle version to install [11g or 18c]: 
(Default version: 11g):
Please input oracle_sid: 
(Default oracle_sid: orcl):

=======================================
Now checking rpm,please wait... 
=======================================

binutils-2.27-34.base.0.1.el7.x86_64
compat-libcap1-1.10-7.el7.x86_64
compat-libstdc++-33-3.2.3-72.el7.x86_64
gcc-c++-4.8.5-36.0.1.el7.x86_64
gcc-gfortran-4.8.5-36.0.1.el7.x86_64
gcc-4.8.5-36.0.1.el7.x86_64
gcc-objc-4.8.5-36.0.1.el7.x86_64
gcc-objc++-4.8.5-36.0.1.el7.x86_64
gcc-gnat-4.8.5-36.0.1.el7.x86_64
gcc-c++-4.8.5-36.0.1.el7.x86_64
glibc-2.17-260.0.9.el7.x86_64
glibc-headers-2.17-260.0.9.el7.x86_64
glibc-common-2.17-260.0.9.el7.x86_64
glibc-devel-2.17-260.0.9.el7.x86_64
glibc-devel-2.17-260.0.9.el7.x86_64
ksh-20120801-139.0.1.el7.x86_64
libaio-0.3.109-13.el7.x86_64
libaio-devel-0.3.109-13.el7.x86_64
libgcc-4.8.5-36.0.1.el7.x86_64
libstdc++-4.8.5-36.0.1.el7.x86_64
libstdc++-devel-4.8.5-36.0.1.el7.x86_64
libstdc++-devel-4.8.5-36.0.1.el7.x86_64
make-3.82-23.el7.x86_64
sysstat-10.1.5-17.el7.x86_64
libXi-1.7.9-1.el7.x86_64
libXtst-1.2.3-1.el7.x86_64

=======================================
++++++++++++++++CHECK PASS!+++++++++++++++++++++ 
=======================================

#########################################################
########           Summary Info              ############
Oracle Home Dir            : /u01/app/oracle/product/11.2.0/dbhome_1
Oracle User Password       : oracle
Oracle Base Dir            : /u01/app/oracle
Oracle SID                 : orcl
Oracle Version             : 11g
#########################################################

Press any key to start...or Press Ctrl+c to cancel

......

=======================================
Now downloading software,please wait... 
=======================================

......

=======================================
Now unziping zip files,please wait...
=======================================

Oracle install pre-setting finish! 

=======================================
Now installing db software,please wait...
=======================================

Starting Oracle Universal Installer...

......

The installation of Oracle Database 11g was successful.

As a root user, execute the following script(s):
    1. /u01/app/oraInventory/orainstRoot.sh
    2. /u01/app/oracle/product/11.2.0/dbhome_1/root.sh


Successfully Setup Software.
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

......

=======================================
Database software installed successfully ... OK! 
=======================================


=======================================
Now installing soft patchs,please wait...
=======================================

......

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17478514  18031668  18522509  19121551  19769489  20299013  20760982  21352635  21948347  22502456  23054359  24006111  24732075  25869727  26609445  26392168  26925576  27338049  27734982  28204707  

Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')


Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms, 11.2.0.4.0...

Patching component oracle.rdbms.rsf, 11.2.0.4.0...


......

Composite patch 28204707 successfully applied.
OPatch Session completed with warnings.

OPatch completed with warnings.

=======================================
Now installing db instance,please wait...
=======================================

Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
74% complete
75% complete
76% complete
77% complete
88% complete
99% complete
100% complete
关注我

链接

分类

最新文章

归档

标签

12c15
18c1
Backup & Recovery7
Internal1
Patch1
asm1
awr1
bbed2
bug1
cbo3
cluster3
cursor1
dataguard4
datapump2
dbua1
dg broker1
dns1
emcc1
histograms1
internal2
json3
linux1
migration1
mmon1
multitenant3
ogg2
ora-8
partition5
pl/sql1
redefinition1
result cache1
rman3
scripts3
sql tuning1
ssh1
statistics5
xtts1
yum1
zabbix3
zabbix preprocessing1
×