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

准确查询表空间使用情况

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

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