Debugging with Git

Debugging with Git

Debugging with Git
Kristina P.
June, 2018
When you are working on a huge project, you may discover bugs in the code that prevent you from proceeding any further in your development. How to fix them?

You can start by looking through your commit history by hand, but this would end up as a very tedious process. Thankfully, Git has multiple tools that can help you hunt for a bug or the culprit when things go wrong.

Git Blame
$ git blame
The git blame command helps you find the commit that created the specific line of code that causes a bug in a specific file of a project. It also determines the author of the commit, making is easier to ask for more information about the code.

You can -L option to limit the line output range.

$ git blame -L 11,21 new_file
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 11) def new
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 12) @article = Article.new
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 13) end
3171aa2dbbce7 (David Smith 2018-05-16 18:21:30 +0200 14) def edit
3171aa2dbbce7 (David Smith 2018-05-16 18:21:30 +0200 15) @article = Article.find(params[:id])
3171aa2dbbce7 (David Smith 2018-05-16 18:21:30 +0200 16) end
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 17) def create
3171aa2dbbce7 (David Smith 2018-05-16 18:21:30 +0200 18) @article = Article.new(article_params)
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 19) if @article.save
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 20) redirect_to @article
^95d69a196b5c7 (Jhon Smith 2018-05-18 13:04:22 +0200 21) else
By following the partial SHA-1 of a commit you can easily see who, when and how was a specific line of code modified. Note that the ^ prefix shows lines that were created in the initial commit and have remained unchanged ever since.

Use -C option to figure out where snippets of code originally came from if they were copied from elsewhere. It tells you the original author and commits regardless of the refactoring done afterward.

$ git blame -L -C 11,21

git blame is helpful when you can assume the cause of the problem. What if you had no idea how to get back to a working state? This is where git bisect comes into play.

Git Bisect
A git bisect is a debugging tool used to find out which specific commit introduced a bug or a problem in the project by doing an automatic binary search. You don’t know what file in the project contains the bug.

If you don’t know what is breaking, and there have been a bunch of commits since the last state where you know the code worked, you’ll likely turn to git bisect for help.

git-bisect

What git bisect does is, it divides the git commit tree into “good”, bug-free commits and “bad” commits by testing them with binary search. Based on the result of the tests, Git navigates through recent commits identifying them, until it finds the culprit. This is known as a binary search algorithm.

If you have multiple bugs, you need to perform a binary search for each of the bugs.

How Does This Work?
First, let’s start with the binary search mode to find a bug: $ git bisect start.
Next, you need to look for a commit where everything was still working. To do so, let’s examine the commit history to find what you need: $ git log –oneline.
–oneline option shows only the names of git commits.

$ git log –oneline
f11c599 Removed unnecessary lines
95d69a1 Added article tests
3171aa2 Enabled editing articles
95d69a1 Added articles
Tag the oldest “good” commit SHA-1: $ git bisect good 95d69a1.
After you have assigned the “good” tag, you need to find a “bad” commit to divide the commit tree where Git can apply the binary search algorithm. Since you know that the latest commit has the error, you will assign it as the “bad” commit: $ git bisect bad f11c599.
Once you have assigned initial and final pointers for your search, Git walks you through the commit history and tags “good” and “bad” commits.
This process continues until you successfully find out the first “bad” commit, the cause of your problem. Now you can exit the git binary search mode by executing: $ git bisect reset.
Git Grep
$ git grep
The git grep command allows you to efficiently and quickly search through your project for a string or regular expression in any of the files in your source code. It avoids searching through .gitignore files.

GREP stands for Global Regular Expression Print.

Additional options:

-n or –line-number: Prints out the line numbers where Git has found matches.
-i or –ignore-case: Ignores case differences between the searched keyword and the file.
-c or –count: Shows the number of matches found in the file for the inputted keyword.
-p or –show-function: Displays the context of the searched keyword.
–and: Ensures multiple matches in the same line of text.
Summary
git blame is a great tool if you know where the buggy code is located. On the other hand, if your repository is considerably large, with a huge commit history that makes it difficult to find the error, git bisect is the way to go. Or you could easily search through your project for a string or regular expression with git grep.

Three debugging tools with three different ways to fix your problems in their own unique ways. Which one did you encounter so far? Share your experience!

Bugs, glitches, and coding problems we know how to solve them all! Subscribe and learn our special techniques.

Become a contributor to our blog
git, debugging
SHOW COMMENTS

Your email address
SUBSCRIBE
or subscribe via RSS with Feedly!
Related posts:
Git Tags
Tags are a simple aspect of Git, they allow you to identify specific release versions

Git Merge vs Rebase
Git merge and rebase serve the same purpose – they combine multiple branches into one. Although

Git Reset, Revert and Checkout
Git toolbox provides multiple unique tools for fixing up mistakes during your development. Commands such

Need help with your digital project
Discover Our Work
Let’s Make Something Awesome!
Company
Bulevar Oslobodjenja 11
21000 Novi Sad, Serbia
facebook twitter linkedin instagram
Navigation
Home
Contact Us
About Us
Portfolio
Open Source
Our Process
Solutions
Design Review
Code Review
Subscribe
Type in your email and receive updates on development, design and startups directly in your inbox.

Your email address
Subscribe
Logo 2010-2018 Kolosek. All Rights Reserved.

Linux: 25 Iptables Netfilter Firewall Examples For New SysAdmins

Linux: 25 Iptables Netfilter Firewall Examples For New SysAdmins

Skip to content

nixCraft
Search MENU
Linux: 25 Iptables Netfilter Firewall Examples For New SysAdmins
Posted on December 13, 2011in Categories Iptables, Linux, Linux distribution, Linux Embedded devices, Linux laptop last updated June 15, 2018

Linux comes with a host based firewall called Netfilter. The netfilter is a set of hooks inside the Linux kernel that allows kernel modules to register callback functions with the network stack. A registered callback function is then called back for every packet that traverses the respective hook within the network stack. This Linux based firewall is controlled by the program called iptables to handles filtering for IPv4, and ip6tables handles filtering for IPv6. I strongly recommend that you first read our quick tutorial that explains how to configure a host-based firewall called Netfilter (iptables) under CentOS / RHEL / Fedora / Redhat Enterprise Linux. If you are using Ubuntu/Debian Linux, see how to setup UFW for more info. This post lists most simple iptables solutions required by a new Linux user to secure his or her Linux operating system from intruders.

Linux Iptables Netfilter Firewall Examples For New SysAdmins
This guide shows essential iptables command to control your daily life firewall rules and security of Linux server running on the bare metal server, router, or cloud server.

Linux Iptables Netfilter Firewall Examples For New SysAdmins
Most of the actions listed in this post written with the assumption that they will be executed by the root user running the bash or any other modern shell. Do not type commands on the remote system as it will disconnect your access.
For demonstration purpose, I’ve used RHEL 6.x, but the following command should work with any modern Linux distro that use the netfliter.
It is NOT a tutorial on how to set iptables. See tutorial here. It is a quick cheat sheet to common iptables commands.
1. Displaying the Status of Your Firewall
Type the following command as root:
# iptables -L -n -v

Sample outputs:

Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination

Chain OUTPUT (policy ACCEPT 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
Above output indicates that the firewall is not active. The following sample shows an active firewall:
# iptables -L -n -v

Sample outputs:

Chain INPUT (policy DROP 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 DROP all — * * 0.0.0.0/0 0.0.0.0/0 state INVALID
394 43586 ACCEPT all — * * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
93 17292 ACCEPT all — br0 * 0.0.0.0/0 0.0.0.0/0
1 142 ACCEPT all — lo * 0.0.0.0/0 0.0.0.0/0

Chain FORWARD (policy DROP 0 packets, 0 bytes)
pkts bytes target prot opt in out source destination
0 0 ACCEPT all — br0 br0 0.0.0.0/0 0.0.0.0/0
0 0 DROP all — * * 0.0.0.0/0 0.0.0.0/0 state INVALID
0 0 TCPMSS tcp — * * 0.0.0.0/0 0.0.0.0/0 tcp flags:0x06/0x02 TCPMSS clamp to PMTU
0 0 ACCEPT all — * * 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
0 0 wanin all — vlan2 * 0.0.0.0/0 0.0.0.0/0
0 0 wanout all — * vlan2 0.0.0.0/0 0.0.0.0/0
0 0 ACCEPT all — br0 * 0.0.0.0/0 0.0.0.0/0

Chain OUTPUT (policy ACCEPT 425 packets, 113K bytes)
pkts bytes target prot opt in out source destination

Chain wanin (1 references)
pkts bytes target prot opt in out source destination

Chain wanout (1 references)
pkts bytes target prot opt in out source destination
Where,

-L : List rules.
-v : Display detailed information. This option makes the list command show the interface name, the rule options, and the TOS masks. The packet and byte counters are also listed, with the suffix ‘K’, ‘M’ or ‘G’ for 1000, 1,000,000 and 1,000,000,000 multipliers respectively.
-n : Display IP address and port in numeric format. Do not use DNS to resolve names. This will speed up listing.
1.1. To inspect firewall with line numbers, enter:
# iptables -n -L -v –line-numbers

Sample outputs:

Chain INPUT (policy DROP)
num target prot opt source destination
1 DROP all — 0.0.0.0/0 0.0.0.0/0 state INVALID
2 ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
3 ACCEPT all — 0.0.0.0/0 0.0.0.0/0
4 ACCEPT all — 0.0.0.0/0 0.0.0.0/0

Chain FORWARD (policy DROP)
num target prot opt source destination
1 ACCEPT all — 0.0.0.0/0 0.0.0.0/0
2 DROP all — 0.0.0.0/0 0.0.0.0/0 state INVALID
3 TCPMSS tcp — 0.0.0.0/0 0.0.0.0/0 tcp flags:0x06/0x02 TCPMSS clamp to PMTU
4 ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
5 wanin all — 0.0.0.0/0 0.0.0.0/0
6 wanout all — 0.0.0.0/0 0.0.0.0/0
7 ACCEPT all — 0.0.0.0/0 0.0.0.0/0

Chain OUTPUT (policy ACCEPT)
num target prot opt source destination

Chain wanin (1 references)
num target prot opt source destination

Chain wanout (1 references)
num target prot opt source destination
You can use line numbers to delete or insert new rules into the firewall.

1.2. To display INPUT or OUTPUT chain rules, enter:
# iptables -L INPUT -n -v
# iptables -L OUTPUT -n -v –line-numbers

2. Stop / Start / Restart the Firewall
If you are using CentOS / RHEL / Fedora Linux, enter:
# service iptables stop
# service iptables start
# service iptables restart

You can use the iptables command itself to stop the firewall and delete all rules:
# iptables -F
# iptables -X
# iptables -t nat -F
# iptables -t nat -X
# iptables -t mangle -F
# iptables -t mangle -X
# iptables -P INPUT ACCEPT
# iptables -P OUTPUT ACCEPT
# iptables -P FORWARD ACCEPT

Where,

-F : Deleting (flushing) all the rules.
-X : Delete chain.
-t table_name : Select table (called nat or mangle) and delete/flush rules.
-P : Set the default policy (such as DROP, REJECT, or ACCEPT).
3. Delete Firewall Rules
To display line number along with other information for existing rules, enter:
# iptables -L INPUT -n –line-numbers
# iptables -L OUTPUT -n –line-numbers
# iptables -L OUTPUT -n –line-numbers | less
# iptables -L OUTPUT -n –line-numbers | grep 202.54.1.1

You will get the list of IP. Look at the number on the left, then use number to delete it. For example delete line number 4, enter:
# iptables -D INPUT 4

OR find source IP 202.54.1.1 and delete from rule:
# iptables -D INPUT -s 202.54.1.1 -j DROP

Where,

-D : Delete one or more rules from the selected chain
4. Insert Firewall Rules
To insert one or more rules in the selected chain as the given rule number use the following syntax. First find out line numbers, enter:
# iptables -L INPUT -n –line-numbers
Sample outputs:

Chain INPUT (policy DROP)
num target prot opt source destination
1 DROP all — 202.54.1.1 0.0.0.0/0
2 ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state NEW,ESTABLISHED
To insert rule between 1 and 2, enter:
# iptables -I INPUT 2 -s 202.54.1.2 -j DROP

To view updated rules, enter:
# iptables -L INPUT -n –line-numbers

Sample outputs:

Chain INPUT (policy DROP)
num target prot opt source destination
1 DROP all — 202.54.1.1 0.0.0.0/0
2 DROP all — 202.54.1.2 0.0.0.0/0
3 ACCEPT all — 0.0.0.0/0 0.0.0.0/0 state NEW,ESTABLISHED
5. Save Firewall Rules
To save firewall rules under CentOS / RHEL / Fedora Linux, enter:
# service iptables save

In this example, drop an IP and save firewall rules:
# iptables -A INPUT -s 202.5.4.1 -j DROP
# service iptables save

For all other distros use the iptables-save command:
# iptables-save > /root/my.active.firewall.rules
# cat /root/my.active.firewall.rules

6. Restore Firewall Rules
To restore firewall rules form a file called /root/my.active.firewall.rules, enter:
# iptables-restore quit
Connection closed.
You can use the nmap command to probe your own server using the following syntax:
$ nmap -sS -p 80 http://www.cyberciti.biz

Sample outputs:

Starting Nmap 5.00 ( http://nmap.org ) at 2011-12-13 13:19 IST
Interesting ports on http://www.cyberciti.biz (75.126.153.206):
PORT STATE SERVICE
80/tcp open http

Nmap done: 1 IP address (1 host up) scanned in 1.00 seconds
I also recommend you install and use sniffer such as tcpdupm and ngrep to test your firewall settings.

CONCLUSION:
This post only list basic rules for new Linux users. You can create and build more complex rules. This requires good understanding of TCP/IP, Linux kernel tuning via sysctl.conf, and good knowledge of your own setup. Stay tuned for next topics:

Stateful packet inspection.
Using connection tracking helpers.
Network address translation.
Layer 2 filtering.
Firewall testing tools.
Dealing with VPNs, DNS, Web, Proxy, and other protocols.
Posted by: Vivek Gite
The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

GOT FEEDBACK? CLICK HERE TO JOIN THE DISCUSSION

SHARE THIS ON (OR READ 81 COMMENTS/ADD ONE BELOW):
Twitter Facebook Google+
Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft takes a lot of my time and hard work to produce. If everyone who reads nixCraft, who likes it, helps fund it, my future would be more secure. You can donate as little as $1 to support nixCraft:

Become a Supporter Make a contribution via Paypal/Bitcoin

RELATED POSTS
How to: Linux flush or remove all iptables rules
Ubuntu Linux install and configure OpenSSH (SSHD) server
Linux Iptables: Block All Incoming Traffic But Allow SSH
Iptables MAC Address Filtering
Linux Iptables open Bittorrent tcp ports 6881 to 6889
How do I build a Simple Linux Firewall for…
Linux Iptables: HowTo Block or Open HTTP/Web Service…
Linux Iptables allow or block ICMP ping request
Linux Iptables: How to specify a range of IP…
Linux Iptables block or open DNS / bind service port 53
Linux Iptables Limit the number of incoming tcp…
How to: Linux Iptables block common attacks
Linux Iptables: How to block or open mail server /…
Linux: Iptables Allow MYSQL server incoming request…
How do I use Iptables connection tracking feature?
Join the discussion at http://www.nixcraft.com
Historical Comment Archive
Comments 81 comment
Happysysadm says: December 13, 2011 at 10:10 am
This is a nice breakdown of IPTABLES indeed! Thank you for taking the time for such a comprehensive explaination… I shall bookmark this!

logicos says: December 13, 2011 at 11:56 am
Try ferm, “for Easy Rule Making” .

In file like “ferm.conf” :

chain ( INPUT OUTPUT FORWARD ) policy DROP;
chain INPUT proto tcp dport ssh ACCEPT;

And next:
ferm -i ferm.conf

Source: http://ferm.foo-projects.org/

LeftMeAlone says: December 13, 2011 at 1:58 pm
Can any one tell me the difference between the DROP vs REJECT? Which one is recommended for my mail server?

Worked says: December 13, 2011 at 2:59 pm
LeftMeAlone, “drop” does not send anything to the remote socket while “reject” sending the following message to the remote socket: (icmp destination port unrechable).

Make clean… “drop” maybe the service does not exists. “reject” you can not access to the service.

Joeman1 says: December 13, 2011 at 3:07 pm
@LeftMeAlone

DROP will silently drop a packet, not notifying the remote host of any problems, just won’t be available. This way, they will no know if the port is active and prohibited or just not used.

REJECT will send an ICMP packet back to the remote host explaining (For the lack of better words) that the host is administratively denied.

The former is preferred as a remote host will not be able to determine if the port is even up.

The latter is not recommended unless software requires the ICMP message for what ever reason. Its not recommended because the remote host will know that the port is in use, but will not be able to connect to it. This way, they can still try to hack the port and get into the system,

Hope this helps!
Joe

Prabal Mishra says: December 13, 2011 at 3:36 pm
thanks !

help for Iptables…………..

smilyface says: December 13, 2011 at 4:11 pm
Thankssss..

noone says: December 13, 2011 at 7:28 pm
how about you try
host -t a http://www.facebook.com
a few times, just to see how dns round-rbin works…

noone says: December 13, 2011 at 7:37 pm
also, you can try this

#!/bin/bash

# Clear any previous rules.
/sbin/iptables -F

# Default drop policy.
/sbin/iptables -P INPUT DROP
/sbin/iptables -P OUTPUT ACCEPT

# Allow anything over loopback and vpn.
/sbin/iptables -A INPUT -i lo -s 127.0.0.1 -d 127.0.0.1 -j ACCEPT
/sbin/iptables -A OUTPUT -o lo -s 127.0.0.1 -d 127.0.0.1 -j ACCEPT
/sbin/iptables -A INPUT -i tun0 -j ACCEPT
/sbin/iptables -A OUTPUT -o tun0 -j ACCEPT
/sbin/iptables -A INPUT -p esp -j ACCEPT
/sbin/iptables -A OUTPUT -p esp -j ACCEPT

# Drop any tcp packet that does not start a connection with a syn flag.
/sbin/iptables -A INPUT -p tcp ! –syn -m state –state NEW -j DROP

# Drop any invalid packet that could not be identified.
/sbin/iptables -A INPUT -m state –state INVALID -j DROP

# Drop invalid packets.
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags FIN,SYN,RST,PSH,ACK,URG NONE -j DROP
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags SYN,FIN SYN,FIN -j DROP
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags SYN,RST SYN,RST -j DROP
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags FIN,RST FIN,RST -j DROP
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags ACK,FIN FIN -j DROP
/sbin/iptables -A INPUT -p tcp -m tcp –tcp-flags ACK,URG URG -j DROP

# Reject broadcasts to 224.0.0.1
/sbin/iptables -A INPUT -s 224.0.0.0/4 -j DROP
/sbin/iptables -A INPUT -d 224.0.0.0/4 -j DROP
/sbin/iptables -A INPUT -s 240.0.0.0/5 -j DROP

# Blocked ports
/sbin/iptables -A INPUT -p tcp -m state –state NEW,ESTABLISHED,RELATED –dport 8010 -j DROP

# Allow TCP/UDP connections out. Keep state so conns out are allowed back in.
/sbin/iptables -A INPUT -p tcp -m state –state ESTABLISHED -j ACCEPT
/sbin/iptables -A OUTPUT -p tcp -m state –state NEW,ESTABLISHED -j ACCEPT
/sbin/iptables -A INPUT -p udp -m state –state ESTABLISHED -j ACCEPT
/sbin/iptables -A OUTPUT -p udp -m state –state NEW,ESTABLISHED -j ACCEPT

# Allow only ICMP echo requests (ping) in. Limit rate in. Uncomment if needed.
/sbin/iptables -A INPUT -p icmp -m state –state NEW,ESTABLISHED –icmp-type echo-reply -j ACCEPT
/sbin/iptables -A OUTPUT -p icmp -m state –state NEW,ESTABLISHED –icmp-type echo-request -j ACCEPT

# or block ICMP allow only ping out
/sbin/iptables -A INPUT -p icmp -m state –state NEW -j DROP
/sbin/iptables -A INPUT -p icmp -m state –state ESTABLISHED -j ACCEPT
/sbin/iptables -A OUTPUT -p icmp -m state –state NEW,ESTABLISHED -j ACCEPT

# Allow ssh connections in.
#/sbin/iptables -A INPUT -p tcp -s 1.2.3.4 -m tcp –dport 22 -m state –state NEW,ESTABLISHED,RELATED -m limit –limit 2/m -j ACCEPT

# Drop everything that did not match above or drop and log it.
#/sbin/iptables -A INPUT -j LOG –log-level 4 –log-prefix “IPTABLES_INPUT: ”
/sbin/iptables -A INPUT -j DROP
#/sbin/iptables -A FORWARD -j LOG –log-level 4 –log-prefix “IPTABLES_FORWARD: ”
/sbin/iptables -A FORWARD -j DROP
#/sbin/iptables -A OUTPUT -j LOG –log-level 4 –log-prefix “IPTABLES_OUTPUT: ”
/sbin/iptables -A OUTPUT -j ACCEPT

iptables-save > /dev/null 2>&1
Coolm@x says: December 13, 2011 at 7:38 pm
Nice examples, but missing one. Commonly searched rule is one for masquerade.

Roy says: December 13, 2011 at 10:19 pm
This is extremely useful, somekind of magic and quick recipe…
(Of course now i can’t send mail on my remote server (to strict rate limit …))

3y3lop says: December 14, 2011 at 3:00 am
Nice examples & thanks.

Jani says: December 15, 2011 at 9:00 am
.. I’m anxiously awaiting similar translated to ip6tables. 🙂

Howard says: December 22, 2011 at 3:24 am
A most excellent presentation of iptables setup and use. Really Superior work. Thanks kindly.

Linus Gasser says: December 22, 2011 at 7:32 pm
Point 8:

And for the private address ranges to block on public interfaces, you’ll also want to block

169.254/16 – zeroconf

Pieter says: December 23, 2011 at 5:44 pm
Nice post, thanks! In example #19 there is an error in the last line:

## open access to mysql server for lan users only ##
iptables -I INPUT -p tcp –dport 3306 -j ACCEPT
Should probably be:

## open access to mysql server for lan users only ##
iptables -I INPUT -p tcp -s 192.168.1.0/24 –dport 3306 -j ACCEPT
shawn cao says: February 24, 2012 at 4:33 am
that is right.

Alejandro says: December 23, 2011 at 11:15 pm
Thanks for this post, I hope you don’t mind if I translate this to spanish and post it on my blog, Mentioning the original source, of course.

Regards

strangr says: December 24, 2011 at 12:41 am
Simple rules to share your connection to internet (interface IFNAME) with other hosts on your local LAN (NATTED_SUBNET).
In other words how to do NAT and MASQEURADEing.

IFNAME=ppp0
NATTED_SUBNET=192.168.2.0/24
# 1) load appropriate kernel module

modprobe iptable_nat
# 2) make sure IPv4 forwarding is enabled

echo 1 > /proc/sys/net/ipv4/ip_forward
# 3) the appropriate rules

iptables -A POSTROUTING -t nat -o $IFNAME -s $NATTED_SUBNET -d 0/0
-j MASQUERADE
iptables -A FORWARD -t filter -o $IFNAME -s $NATTED_SUBNET -m state
–state NEW,ESTABLISHED,RELATED -j ACCEPT
iptables -A FORWARD -t filter -i $IFNAME -d $NATTED_SUBNET -m state
–state ESTABLISHED,RELATED -j ACCEPT
liRONux says: July 8, 2013 at 12:50 pm
THANKS for this.
How about blocking a website while having those rules?

JD says: December 31, 2011 at 2:27 am
## open access to mysql server for lan users only ##
iptables -I INPUT -p tcp -s 192.168.1.0/24 –dport 3306 -j ACCEPT

This should be like this:

-s 192.168.1.0/24 -d 192.168.2.2 -i eth0 -p tcp -m state –state NEW -m tcp –dport 3306 -j ACCEPT

a rule like this should go under RELATED,ESTABLISHED in the INPUT chain

JD says: December 31, 2011 at 2:39 am
For email servers, I have rate limiting rules in place for all service ports.

In the INPUT chain I have the spam firewall ip(s), allowed via port 25.

Then for the email ports, I impose a hit count of 10 in 60 seconds, smart phones, email clients do not poll every second. Anything more than this is dropped and they can continue on a rampage with no affect on the server(s). It took me a while to come up with the rate-limiting chains to work with the email server. Since the Watch Guard XCS devices needed to be exempt from the rules. They have rate-limits on incoming connections as well, a lot better than Barracuda.

I always specify the source/destination interface, state then the port.

MB says: January 3, 2012 at 8:17 am
How do i open the port 25 on a public ip (eg. 1.2.3.4) because it is close, I can only send email but can’t receive email?
But on my localhost it’s open, when I test I able to send and receive only on 127.0.0.1. This is my rule

iptables -A INPUT -p tcp -m tcp –dport 25 -j ACCEPT

when i check netstat -tulpn | grep :25
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2671/exim4
tcp6 0 0 ::1:25 :::* LISTEN 2671/exim4

Hope you can help me on this matter. I really confused on this one.

Badr Najah says: January 2, 2012 at 6:55 pm
Very useful.
Thanks

dilip says: January 5, 2012 at 7:36 am
Wooooooooooowwwwww. thats coooool…
very usefull link….

Thanks yar….

nbasileu says: January 9, 2012 at 10:19 am
Rule #14

## *only accept traffic for TCP port # 8080 from mac 00:0F:EA:91:04:07 * ##
# iptables -A INPUT -p tcp –destination-port 22 -m mac –mac-source 00:0F:EA:91:04:07 -j ACCEPT

–destination-port 8080 not 22

Anyway, this is a fu**** good website with fully nice articles.
Very big thx dudes.

Happy new year everyone.

Atul Modi says: March 11, 2012 at 10:16 am
Excellent Stuff Guys!!!

Everyone is putting their part. Great to see this kind of community flourish further.

I am thankful to the ppl who started this website.

Daniel Vieceli says: March 13, 2012 at 2:38 pm
Excellent thanks.

jm says: April 1, 2012 at 3:48 am
Good info and well written.Easy to understand for everyone… I will be back to learn more needed security rules.. Oh and yes I’m a human but I hate to say the definition of human is ( MONSTER) don’t believe me ? Look it up on the net ! Ha ha ha ha
Thank you for this page….

rw1 says: April 5, 2012 at 7:45 am
thank you! for the information on how to delete a firewall rule! priceless! thanks!

Eli says: May 11, 2012 at 12:19 am
How can i use iptable rules to use multiple internet connections for the same bit torrent download?
Actually, i have two broadband connections. I want to combine them. I am told to get load balancing hardware and i cant afford that. So, i did some experimenting. On first DSL modem, i set its IP to be 192.168.1.1
On second modem, i set its IP to be 192.168.2.1
Then in windows network adapter settings, i set Metric value of each adapter to 1. Thats about it. My bit torrent downloads/uploads use both my internet connections at the same time which gives effect of combined speed.
Can i do something like that in Linux?
Or, how can i combine two internet connections by using iptables? I dont want any hardware changes. All i have is two DSL modems and two network interface cards. Precise help would be greatly appreciated.

kolya says: May 13, 2012 at 6:55 pm
Hi, got a question to the author of the article. I have tried different kind of commands from the command line, edited the file /etc/sysconfig/iptables directly with following saving and restarting iptables/rebooting system. Nothing helps, my rules get overwritten by the system flushing my new rules or editing them. I tried to open ports (22,21 etc). The goal why I edit my firewall is to get connected to ftp server via FileZilla. Would you recommend me how to open ports? Tell me please if you need any system outputs or something. Cheers

nixCraft says: May 13, 2012 at 8:35 pm
> my rules get overwritten by the system flushing my new rules or editing them

I think you got some sort of script or other firewall product running that is overwriting your rules. Check your cron job and you find the source for the same. If you need further assistance head over to the nixcraft Linux Support forum.

kolya says: May 14, 2012 at 12:21 pm
thanks for your respond, as I am not a specialist I didn’t any changes to my crontab yet, anyway I checked it, also /cron.d and everything connected to cron in /var/spool/…. Nothing about iptables or something. What I noticed there are several iptables files in /etc/sysconfig/: iptables.old written by system-config-firewall, iptables generated by iptables-save with some changes what I didn’t entered.
Here is what I entered from wiki.centos.org/HowTos/Network/IPTables:

# iptables -P INPUT ACCEPT
# iptables -F
# iptables -A INPUT -i lo -j ACCEPT
# iptables -A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT
# iptables -A INPUT -p tcp –dport 22 -j ACCEPT
# iptables -P INPUT DROP
# iptables -P FORWARD DROP
# iptables -P OUTPUT ACCEPT
# iptables -L -v

Here is what I got in the iptables’s file:

:INPUT DROP [1:40]
:FORWARD DROP [0:0]
:OUTPUT ACCEPT [526:43673]
-A INPUT -i lo -j ACCEPT
-A INPUT -m state –state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp –dport 22 -j ACCEPT
COMMIT
Don’t know why it changes, probably it is aplying kind of default settings, but analyzing this settings the port 22 should be open. Nmap says it is closed, telnet outputs connection refused. Was trying to set samba server with the same result due to my firewall. What to do?

Sigma says: May 25, 2012 at 6:53 am
Thanks a lot for this article, which is extremely easy to understand and follow for beginners as me!

dima says: June 9, 2012 at 10:38 am
Hi
Regarding the block #7.1: Only Block Incoming Traffic
The rule
# iptables -A INPUT -m state –state NEW,ESTABLISHED -j ACCEPT
looks dubious to me
Why would you want to allow NEW connections?
In my view it should read
# iptables -A INPUT -m state –state RELATED,ESTABLISHED -j ACCEPT

qubits4all says: February 2, 2013 at 8:08 am
I noticed this as well. The rule as given is not right. I’ve been using iptables for a couple of years now, and the INPUT rule here should read:
iptables -A INPUT -m state –state ESTABLISHED,RELATED
(actually the above order is equivalent), because one clearly wouldn’t want to match the NEW state here. Doing so would open up the door to TCP connects (i.e., TCP SYN packets) to any listening TCP services, as well as to UDP datagrams.

Cheers to the author(s) of nixCraft for a nice article & a useful collection of iptables rules. This has become one of my favorite Linux/Unix blogs, so please keep the articles coming.

BiBi says: June 21, 2012 at 3:24 am
Thank you very much, this site is very useful. I love all of you.

Juan says: July 14, 2012 at 1:53 pm
Hi.
Excellent tutorial. My desire is to block social networking in my job, I did it with squid in transparent mode but skipped to enter https. I did the tests on a virtual pc and it worked fine. The issue is that I is working on the production server. This has two network cards, eth0 traffic where it enters the Internet and eth1 to connect to the network. For the case of Facebook do the following:

# We block Facebook
iptables-A OUTPUT-p tcp-d 69.63.176.0/20-dport 443-j DROP
iptables-A OUTPUT-p tcp-d 66.220.144.0/20-dport 443-j DROP
iptables-A OUTPUT-p tcp-d 69.171.224.0/19-dport 443-j DROP
iptables-A OUTPUT-p tcp-d http://www.facebook.com-dport 443-j DROP
iptables-A OUTPUT-p tcp-d facebook.com-dport 443-j DROP

Any suggestions?.

Greetings.

jaydatt says: August 30, 2012 at 10:47 am
really helpful article

Borislav Bozhanov says: September 11, 2012 at 11:13 pm
Hi,

Here is how to BLOCK FACEBOOK with single line command and iptables:

for i in $(nslookup facebook.com|grep Address|grep -v “#53″|awk ‘{print $2}’); do iptables -I FORWARD -m tcp -p tcp -d $i/24 –dport 443 -j DROP; done

You can replace the website with any other secure (https) you want.

For http websites (non-secure) – use the following line, replacing yahoo.com with the desired domain name:
for i in $(nslookup yahoo.com|grep Address|grep -v “#53″|awk ‘{print $2}’); do iptables -I FORWARD -m tcp -p tcp -d $i/24 –dport 80 -j DROP; done

Don’t forget to save your iptables configuration.

Regards,
Borislav Bozhanov

Łukasz Bodziony says: September 13, 2012 at 7:37 pm
Thank you!!!

Gus says: September 29, 2012 at 6:51 pm
Hello.
I’m working with virtual machines. and would like to make a firewall and rootin bash.

My question is this
I have several public ip — IP1 = (200.45.xx.xxx) IP2 (=200.xx), IP3 = ·

The issue is that one of them use to Wan IP1.

Now I want to direct traffic from outside to inside. But I also want to redirect the traffic that comes to public ip 2 ( IP2 to the local machine in lan ( 192.168.1.2) and what comes to public ip 3 (IP3) to the local machine (192.168.1.3)

I can not find examples of how to redirect traffic coming to a specific public IP to a particular LAN private IP.
If you can ask to help me.

#!/bin/sh

## SCRIPT de IPTABLES
## Pello Xabier Altadill Izura

echo -n Aplicando Reglas de Firewall…

## Paramos el ipchains y quitamos el modulo
/etc/rc.d/init.d/firewall stop
rmmod ipchains

## Instalando modulos
modprobe ip_tables
modprobe ip_nat_ftp
modprobe ip_conntrack_ftp

## Variables
IPTABLES=iptables
EXTIF=”eth1″
INTIF=”eth0″

## En este caso,
## la tarjeta eth1 es la que va al ROUTER y la eth0 la de la LAN

## Primeras reglas
/sbin/iptables -P INPUT DROP
/sbin/iptables -F INPUT
/sbin/iptables -P OUTPUT ACCEPT
/sbin/iptables -F OUTPUT
/sbin/iptables -P FORWARD ACCEPT
/sbin/iptables -F FORWARD
/sbin/iptables -t nat -F

### En principio, si las reglas INPUT por defecto hacen DROP, no haria falta
### meter mas reglas, pero si temporalmente se pasa a ACCEPT no esta de mas.

## Todo lo que viene de cierta IP se deja pasar (administradores remotos…)
/sbin/iptables -A INPUT -i $EXTIF -s 203.175.34.0/24 -d 0.0.0.0/0 -j ACCEPT

## El localhost se deja
/sbin/iptables -A INPUT -i lo -j ACCEPT
/sbin/iptables -A OUTPUT -o lo -j ACCEPT

## Aceptar al exterior al 80 y al 443

# Permitir salida al 80
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 80 -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –dport 80 -j ACCEPT
# Permitir salida al 443
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 443 -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –dport 443 -j ACCEPT

## SALIDA SMTP – Para que el servidor se pueda conectar a otros MTA
# Permitir salida SMTP
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 25 -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –dport 25 -j ACCEPT

## SALIDA FTP – Para que el servidor se pueda conectar a FTPs
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 21 -m state –state ESTABLISHED -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –dport 21 -m state –state NEW,ESTABLISHED -j ACCEPT
# ftp activo
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 20 -m state –state ESTABLISHED,RELATED -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –dport 20 -m state –state ESTABLISHED -j ACCEPT
# ftp pasivo
/sbin/iptables -A INPUT -i $EXTIF -p tcp –sport 1024:65535 –dport 1024:65535 -m state –state ESTABLISHED -j ACCEPT
/sbin/iptables -A OUTPUT -o $EXTIF -p tcp –sport 1024:65535 –dport 1024:65535 -m state –state ESTABLISHED,RELATED -j ACCEPT
Rogier says: October 23, 2012 at 5:48 am
Hi, I have two interfaces: eth0 (for internal network) and eth1 (WAN). The server does the routing to the clients with the following IPtables:

# Generated by iptables-save v1.4.12 on Fri Oct 19 21:14:26 2012
*nat
:PREROUTING ACCEPT [14:1149]
:INPUT ACCEPT [6:625]
:OUTPUT ACCEPT [4:313]
:POSTROUTING ACCEPT [0:0]
-A POSTROUTING -o eth1 -j MASQUERADE
COMMIT
# Completed on Fri Oct 19 21:14:26 2012
# Generated by iptables-save v1.4.12 on Fri Oct 19 21:14:26 2012
*filter
:INPUT ACCEPT [505:53082]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [247:29622]
-A FORWARD -s 192.168.1.0/24 -i eth0 -o eth1 -m conntrack –ctstate NEW -j ACCEPT
-A FORWARD -m conntrack –ctstate RELATED,ESTABLISHED -j ACCEPT
COMMIT
# Completed on Fri Oct 19 21:14:26 2012
This works fine, however I have no other rules set up. Can anyone help me in deciding what rules I need? the server (who does the NAT) is also running a webserver on port 80, SSH server on 22. All other ports can may be blocked.. how can I achieve this?

Jorge Robles says: October 24, 2012 at 2:37 pm
I use fwbuilder to create my rules, this interface “looks like” checkpoint’s fw1 client to edit rules. Very graphical, and good to work with.

sahil says: November 8, 2012 at 10:14 am
very nice and informative article
it really helped to work for my VPS server

bussy says: November 9, 2012 at 8:09 pm
how i do give access ip ex 192.168.0.2 only for facebook .

Sayajin says: December 19, 2012 at 7:27 am
fb_address=$(dig facebook.com +tcp +short);
iptables -A OUTPUT -p tcp -s !192.168.0.2/32 -d $fb_address -j DROP;

bahathir says: November 25, 2012 at 3:17 am
For tip #2, it is advisable to run the -P chain ACCEPt first, before flushing it.
Exampes
# iptables -P INPUT ACCEPT
# iptables -P OUTPUT ACCEPT
# iptables -P FORWARD ACCEPT
# iptables -F
# iptables -t nat -F
# iptables -X
#iptables -t nat -X

Why? If the current chain’s policy is DROP, and you are remotely accessing to the server via SSH, and the rule “-A INPUT -p tcp –dport 22 -j ACCEPT” is still opens the “-P INPUT DROP”. you may disconnected as soon as you flush *-F* the rules, and the default policy “-P INPUT DROP” kicks in. 🙂 If you are working on the local console, it is fine.

Thank you.

qubits4all says: February 2, 2013 at 8:34 am
This is a valid point. Another way to avoid locking oneself out, which I have found very useful for testing firewall changes over an SSH session, is the iptables-apply command (incl. with the Ubuntu iptables package for e.g.). It functions essentially the same as the iptables command, but when applying a rule change it prompts w/ a timeout for a confirmation after making the change. If no response is received (in 30 secs. by default), then it rolls back the change (i.e., add, modify, delete or otherwise).

Once rules have been tested, I save them with iptables-save, and load the stable configuration with an init.d script at system startup (and including support for a ‘restart’ command here, for a clean flush, delete & reapply rules cycle).

levi says: November 27, 2012 at 4:24 am
Could it be you are using iptables save after directly editing? This will overwrite your work. Do a restart to load your newly edited table.

KeepEnEmUp says: December 8, 2012 at 2:32 am
Great Thx for awesome site and for awesome reading,tutos.
Respect And KeepUp dude!

rashid Iqbal says: December 13, 2012 at 11:43 am
from graphical user and groups If I add or delete any user I can’t see any reference log nor in messages or in /var/log/secure file,

Kindly please advise on this that from GUI if we run/execute any command where does the log message will go.

Gangadhar says: February 27, 2013 at 2:50 pm
thank you very much for such a wonderful explanation….. very clear and had nice experience with iptables…

haidt says: March 10, 2013 at 9:04 am
Hi there,

i have a problem, i have got a server and LAN network, and this’s feature

internet (eth0) server (eth1) clients -> 10.0.0.2
-> 10.0.0.3
now, i can config to iptables accept all client connect internet, but in this situation, i want to allow only one client (assume: 10.0.0.3), i try but not completed. pls help me 🙂

Thanks

Manish Narnaware says: April 24, 2013 at 5:33 am
Thanks a lot.

Orange says: April 25, 2013 at 11:08 pm
Thank you very much. Coincidentally, I just discovered an hour ago that I need to use iptables to allow a tablet computer to talk through my laptop, using the same internet connection. And then I discovered that I can’t remember any of it. I was using IP tables and IP nat 15 years ago, back when it was Darrin Reed’s project (name???), but that was too long ago for my memory. This article will get me back on track fast.
Thanks again.

Le Vu says: May 29, 2013 at 8:23 am
Module xt_connlimit was disabled. How to limit number of connection per IP, can you module limit and recent. Please help me. 🙂

abedatwa says: June 13, 2013 at 7:08 am
thank you

abedatwa says: June 13, 2013 at 7:09 am
thank you for you ivitation

Mark says: August 1, 2013 at 1:33 pm
Thank you for this example. I don’t remember the command line off the top of my head and this gives me enough information to do what I need to do without having to read 30 man pages. If only proper support (support.oracle.com) would be so efficient.

paul says: August 3, 2013 at 1:29 am
Enjoyed and appreciated the article and the comments particularly from noone (13 December 2011). I’ve added some of the suggestions to my firewalls.

The first lines in every INPUT are always

-A INPUT -s 123.123.123.123/32 -j ACCEPT
-A INPUT -s 124.124.124.124/32 -j ACCEPT

123 & 124 represent my external IPs including home and office backup connections.

These entries ensure that whatever errors I make in IPTables I can never lock myself out of my remote servers.

Best regards to all,

Paul.

John says: August 21, 2013 at 2:40 am
In 7.1, the example provided does not block all incoming traffic like it claims. If you don’t add more parameters, the rule will apply to both directions.

The example rule:

iptables -I INPUT -m state –state NEW,ESTABLISHED -j ACCEPT

This would not only allow for NEW outgoing requests but also NEW incoming requests. The DROP policy for the INPUT chain can’t do it’s job to block incoming connections since it is applied after the rule which allows both NEW incoming and outgoing connections.

sophea says: October 30, 2013 at 8:53 am
I have problem when i add by manually (ex: #iptables -A INPUT -s 192.168.0.1 -p tcp –dport 53 -j ACCEP) but when i restart iptables by service iptables restart it not work because :

1- when i view in /etc/sysconfig/iptables the IP address will be 192.168.0.1/32 but my land /24
2- problem when i start or stop by system-config-firewall

Can u help me pls?

Mohammad says: February 27, 2014 at 3:38 pm
Hi, I have a question. Could we log packets which are dropped because of forwarding queue is filled (e.g in congestion time)? How do I perform this work?
Regards Mohammad.

juan-vargas says: May 26, 2014 at 12:49 am
Hi there. Greetings from Mexico. Nice examples. Very usefull all of them. But, can I bypass traffic in the port-80 once my iptables-policies are: -P INPUT DROP, -P OUTPUT DROP, -P FORWARD DROP?

thank you all in advance.

Anumod says: August 4, 2014 at 12:47 pm
How to disable sending back TCP Reset to clients or how to increase TCP reset timeout in iptable.
(I am using a raw socket as server and able to receive tcp client SYN request, but before sending SYNACK, tcp reset packet is going from server)

John says: August 9, 2014 at 1:20 am
Hi Guys,

New to IP Tables, need a little advice – I have a guest wifi network setup, how do I block port 25 outgoing for an ip range?

Thanks, John Tankard

Darko Vrsic says: October 15, 2014 at 9:30 am
Very nice!

Thank you!

Bas says: October 15, 2014 at 5:52 pm
Nice breakdown on iptables!

However, I prefer (& recommend) to use a firewall manager (command-line / config file based tool) like shorewall:

http://shorewall.net/

Ron Barak says: December 1, 2014 at 4:43 pm
Useful page.
Here’XXX errXXX I found
#18: Established Connections and >>>Restaring<<>>Restaring<<>>Restaring<<< The Linux Firewall

Chống Trộm Quảng Ngãi says: April 30, 2016 at 3:36 am
CSF firewall base on iptables is very good for me. I think new SysAdmins should using CSF firewall.

DECPNQ says: August 23, 2016 at 3:41 pm
Wow. Awesome examples. I loved it. Thanks!!!!!!!!!!!

Paran says: January 5, 2017 at 5:27 pm
Thanks to all, really this site is excellent to gather knowledge.
Right now my boss has assigned me a new task. I would like to share this also need to solve the problem. Please help me.
How to blocked an IP address which is request in my server port (Tomcat) more or equal twenty times per second.
Suppose my server ip is 123.123.123.123 with it’s port is 80. However, another IP address is concurrently requested in my server. IP address may be different. But, it can be identified by their nature. Per second can attack in my server more that 20 times.

ben says: January 29, 2017 at 9:30 pm
can someone put all the recommend lines in one file?
i see the comments here , and i didn’t get it what to put

Mandar says: June 9, 2017 at 7:36 am
Hii All,
Can any one help me for security.
I want to accept traffic over UDP from particular MAC or device

Have a question? Post it on our forum!

Post navigation
Previous post:
Previous post:Download CentOS 6.1 CD / DVD ISO
Next post:
Next post:Linux / UNIX Desktop Fun: Let it Snow On Your Desktop
Tagged as: Tags /etc/sysconfig/iptables, /var/log/messages, Centos iptables rules examples, Debian iptables rules examples, enterprise linux, Fedora iptables rules examples, firewall iptables, iptables command, iptables rules example, iptables rules examples, kernel modules, linux distro, linux kernel, netfilter, RHEL iptables rules examples, Slackware iptables rules examples, Ubuntu iptables rules examples
nixCraft @2000-2018 nixCraft. All rights reserved.
PRIVACY
TERM OF SERVICE
CONTACT/EMAIL
DONATIONS
Hosted by Linode
DNS & CDN by Cloudflare
Designed and Developed by Prospect One Prospect One

Umgang mit virtuellen Spalten in Oracle 11g

Umgang mit virtuellen Spalten in Oracle 11g

Umgang mit virtuellen Spalten in Oracle 11g
von Frank Schneede, ORACLE Deutschland GmbH
Die Verwendung von Ausdrücken wird durch die Oracle Datenbank schon seit langem unterstützt. Ein Beispiel hierfür ist die Verwendung von Ausdrücken in Views oder function-based Indices. Üblicherweise werden Views dafür genutzt, Berechnungen transparent für den Anwender abzubilden. Das geschieht, indem das Ergebnis einer Berechnung aus Spalten der zugrundeliegenden Tabelle als neue Spalte in einer View bereitgestellt wird. Seit Oracle 8i besteht die Möglichkeit, Ausdrücke zu indizieren, welches über die Verwendung von function-based Indices geschieht. In Oracle 11g können nun Ausdrücke als virtuelle Spalten direkt in den Basistabellen gespeichert werden.

Dieser Artikel zeigt auf, wie flexibel der Einsatz von virtuellen Spalten im Vergleich zu den geschilderten Alternativen ist. Hierbei geht es um die Grundlagen der Verwendung von virtuellen Spalten und die verschiedenen Aspekte, die beim Umgang mit virtuellen Spalten zu beachten sind:

Anlegen einer virtuellen Spalte
Indices und Constraints
Hinzufügen virtueller Spalten
Virtuelle Spalten und PL/SQL Funktionen
Virtuelle Spalten im Data-Dictionary
Virtuelle Spalten, Speicherplatz und der Cost Based Optimizer (CBO)
Virtuelle Spalten und Partitionierung
Einschränkungen bei der Nutzung von Virtuellen Spalten

Anlegen einer virtuellen Spalte

Eine Tabelle, die eine virtuelle Spalte enthält, wird über ein einfaches syntakisches Konstrukt angelegt:
SQL> CREATE TABLE t_test
2 ( c_col1 INTEGER
3 , c_col2 INTEGER
4 , c_col3 INTEGER GENERATED ALWAYS AS (c_col1 + c_col2) VIRTUAL
5 );

Table created.

SQL>

Wie man erkennen kann, wird die virtuelle Spalte über einen einfachen Ausdruck auf Basis der anderen Spalten der Tabelle erzeugt. Das Schlüsselwort VIRTUAL ist hierbei optional, jedoch erleichtert seine Verwendung die Lesbarkeit des Statements. Werte in virtuellen Spalten werden nicht abgespeichert, sondern lediglich zur Laufzeit berechnet. Dazu wird der Ausdruck – im obigen Beispiel also c_col1 + c_col2 – benutzt. Diese Vorgehensweise spart auf der einen Seite natürlich Platz, auf der anderen Seite sind jedoch ein paar Dinge zu beachten, die das Einfügen von Daten betreffen.
SQL> INSERT INTO t_test VALUES (10, 20, 30);
INSERT INTO t_test VALUES (10, 20, 30)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

SQL>

Wie man sieht, ist das explizite Füllen einer virtuellen Spalte nicht möglich. Aus diesem Grunde versuchen wir nun, lediglich die physisch vorhandenen Spalten anzusprechen.
SQL> INSERT INTO t_test VALUES (10, 20);
INSERT INTO t_test VALUES (10, 20)
*
ERROR at line 1:
ORA-00947: not enough values

SQL>

Auch dieser Versuch schlägt fehl. Es ist also nicht möglich, virtuelle Spalten in INSERT oder UPDATE Statements direkt anzusprechen. Trotzdem gehören auch virtuelle Spalten natürlich zur Beschreibung der Tabelle. Das bedeutet, dass die Spalten in dem oben gezeigten INSERT Statement explizit referenziert werden müssen.
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20);

1 row created.

SQL>

Da vollständig qualifizierte SQL Statements ohnehin für jeden DBA eine “best practise” darstellen sollte, ist diese Einschränkung de facto irrelevant. Nun ist unsere Beispieltabelle also mit Daten gefüllt und kann abgefagt werden.
SQL> SELECT * FROM t_test;

C_COL1 C_COL2 C_COL3
———- ———- ———-
10 20 30

SQL>

Indices und Constraints

Virtuelle Spalten können für Indices und Constraints verwendet werden. Hierbei wird der Wert der virtuellen Spalte im Index gespeichert. In dem folgenden Beispiel wird ein Primärschlüssel Constraint auf der virtuellen Spalte c_col3 angelegt.
SQL> CREATE UNIQUE INDEX t_test_pk ON t_test(c_col3);

Index created.

SQL> ALTER TABLE t_test ADD
2 CONSTRAINT t_test_pk
3 PRIMARY KEY (c_col3)
4 USING INDEX;

Table altered.

SQL>

Beim Versuch des Einfügens eines neuen Datensatzes mit Werten, die einen schon vorhandenen Wert in der virtuellen Spalte ergeben würden, erfolgt eine Verletzung des Primärschlüssel Constraints.
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20);
INSERT INTO t_test (c_col1, c_col2) VALUES (10, 20)
*
ERROR at line 1:
ORA-00001: unique constraint (VTEST.T_TEST_PK) violated

SQL>

Wie erwartet, wird eine ORA-00001 Fehlermeldung ausgegeben.

Wie man sieht, stellt das Anlegen eines Primärschlüssels keine besondere Herausforderung dar. Im folgenden Beispiel wird nun eine Detail-Tabelle angelegt, deren Fremdschlüssel Constraint auf die virtuelle Spalte c_col3 der Testtabelle verweist.
SQL> CREATE TABLE t_test_detail
2 ( c_col3 INTEGER
3 , CONSTRAINT t_test_detail_fk
4 FOREIGN KEY (c_col3)
5 REFERENCES t_test(c_col3)
6 );

Table created.

SQL>

Beim Einfügen gültiger und ungültiger Datensätze verhält sich das System wie erwartet.
SQL> INSERT INTO t_test_detail VALUES (30);

1 row created.

SQL> INSERT INTO t_test_detail VALUES (40);
INSERT INTO t_test_detail VALUES (40)
*
ERROR at line 1:
ORA-02291: integrity constraint (VTEST.T_TEST_DETAIL_FK) violated – parent key not found

SQL>

Hinzufügen virtueller Spalten

Selbstverständlich können virtuelle Spalten auch zu einer bereits bestehenden Tabelle mit einem ALTER TABLE Befehl hinzugefügt werden. Im folgenden Beispiel wird eine weitere virtuelle Spalte ergänzt und mit einem zusätzlichen Check-Constraint versehen.
SQL> ALTER TABLE t_test ADD
2 c_col4 GENERATED ALWAYS AS (c_col1 * c_col2)
3 CHECK (c_col4 >= 10);

Table altered.

SQL>

Wie bereits oben ausgeführt, wird im Index der Wert der virtuellen Spalte als Ergebnis des zugrundeliegenden Ausdrucks abgespeichert. Ein Check-Constraint wertet zum Zeitpunkt der INSERT- oder UPDATE-Operation den Ausdruck auf Basis der enthaltenen Daten aus. Dieses erscheint offensichtlich, denn ein Check-Constraint besitzt ja keine Datenstruktur, also keinen Index, in dem Werte abgelegt sein könnten.

Auf unserer neuen virtuellen Spalte c_col4 stellt der Check-Constraint sicher, dass das Produkt der Spalten c_col1 und c_col2 größer als 10 ist. Ein kleiner Test zeigt, dass der Check-Constraint funktioniert.
SQL> INSERT INTO t_test (c_col1, c_col2) VALUES (1, 2);
INSERT INTO t_test (c_col1, c_col2) VALUES (1, 2)
*
ERROR at line 1:
ORA-02290: check constraint (VTEST.SYS_C0011537) violated

SQL>

Virtuelle Spalten und PL/SQL Funktionen

Es ist möglich, virtuelle Spalten über PL/SQL Funktionen zu versorgen, das heißt als Ganzes oder als Teil des gesamten Ausdruckes. Die einzige Bedingung, die an die verwendete PL/SQL Funktion gestellt wird, ist die, dass die Funktion deterministisch sein muss. Die Funktion kann in einem Package enthalten sein. Im folgenden Beispiel wird eine deterministische Funktion erstellt, die die Summe zweier Eingabe-Parameter zurückliefert.
SQL> CREATE FUNCTION f_summiere(
2 p1 IN INTEGER,
3 p2 IN INTEGER ) RETURN INTEGER DETERMINISTIC AS
4 BEGIN
5 RETURN p1 + p2;
6 END f_summiere;
7 /

Function created.

SQL>

Das Schlüsselwort DETERMINISTIC muss hier angegeben werden um sicherzustellen, dass diese Funktion in einer virtuellen Spalte verwendet werden darf. Eine weitere virtuelle Spalte c_col5 wird zu unserer Testtabelle hinzugefügt. Diese Spalte enthält die Summe der Spalten c_col1 und c_col2, die hier jedoch mittels der Funktion f_summiere gebildet wird. Anschließend wird die neue virtuelle Spalte abgefragt.
SQL> ALTER TABLE t_test ADD
2 c_col5 GENERATED ALWAYS AS (f_summiere(c_col1,c_col2));

Table altered.

SQL> SELECT c_col1, c_col2, c_col5 FROM t_test;

C_COL1 C_COL2 C_COL5
———- ———- ———-
10 20 30

SQL>

Man sieht, dass die neue Spalte c_col5 den gleichen Wert hat, wie die virtuelle Spalte c_col3. Es ist für das Verständnis des Datenmodells hilfreich, Ausdrücke über Programmkonstrukte (Funktionen, Packages) zu definieren, jedoch ist dieses Vorgehen mit Auswirkungen auf die Performance verbunden. Dieses zeigt sich an einem kleinen Beispiel, in dem 1.000.000 Zeilen mit den Spalten c_col3 und c_col5 abgefragt werden. Zuerst müssen diese Testdaten jedoch erzeugt werden.
SQL> INSERT INTO t_test (c_col1, c_col2)
2 SELECT ROWNUM*10, TRUNC(ROWNUM/20)+1
3 FROM dual
4 CONNECT BY ROWNUM
SQL> COMMIT;

Commit complete.

SQL>

Jetzt befinden sich also 1 Million Testdatensätze in unserer Tabelle. Diese werden über die Spalten c_col3 und c_col5 in zwei unterschiedlichen Select-Statements abgefragt. Um die Auswirkungen auf die Performance sehen zu können, wird Autotrace aktiviert und Timing gesetzt.
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> SELECT c_col3 FROM t_test;

1000000 rows selected.

Elapsed: 00:00:05.43

Statistics
———————————————————-
28 recursive calls
3 db block gets
69746 consistent gets
0 physical reads
70956 redo size
15556478 bytes sent via SQL*Net to client
733745 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

SQL> SELECT c_col5 FROM t_test;

1000000 rows selected.

Elapsed: 00:00:09.93

Statistics
———————————————————-
11 recursive calls
0 db block gets
69698 consistent gets
0 physical reads
64312 redo size
15556478 bytes sent via SQL*Net to client
733745 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

SQL>

In unserem Vergleich benötigt die Abfrage der virtuellen Spalte c_col5, die auf einer PL/SQL Funktion basiert, ungefähr doppelt so lange wie die Abfrage auf Basis des Ausdruckes (c_col3). Der I/O-Aufwand für beide Abfragen ist annähernd gleich. Bei einfachen Ausdrücken wie in unserem Beispiel macht es also durchaus Sinn, die Berechnungslogik in die virtuellen Spalten zu integrieren und den Nachteil in der Performance zu vermeiden. Virtuelle Spalten auf Basis von PL/SQL Funktionen mit einer geringen Kardinalität können von dem in Oracle 11g eingeführten Feature des Result Caches profitieren. Ein Performancenachteil gegenüber den “inline” definierten Ausdrücken wird sich dennoch zeigen.

Virtuelle Spalten im Data-Dictionary

Beim Design von Datenodellen ist es wichtig, diese so zu gestalten, dass mit der Abfrage des Data-Dictionary das System möglichst selbsterklärend beschrieben werden kann. Hierzu sind Kommentare sehr hilfreich, die für physisch vorhandene und virtuelle Spalten angelegt werden sollten, wie im folgenden Beispiel gezeigt.
SQL> COMMENT ON COLUMN t_test.c_col3 IS ‘Virtual column [c_col1 + c_col2]’;

Comment created.

SQL> COMMENT ON COLUMN t_test.c_col4 IS ‘Virtual column [c_col1 * c_col2]’;

Comment created.

SQL> COMMENT ON COLUMN t_test.c_col5 IS ‘Virtual column [f_summiere(c_col1,c_col2)]’;

Comment created.

SQL> SELECT column_name
2 , comments
3 FROM user_col_comments
4 WHERE table_name = ‘T_TEST’;

COLUMN_NAME COMMENTS
—————————— ———————————————
C_COL1
C_COL2
C_COL3 Virtual column [c_col1 + c_col2]
C_COL4 Virtual column [c_col1 * c_col2]
C_COL5 Virtual column [f_summiere(c_col1,c_col2)]

SQL>

Informationen über virtuelle Spalten können an unterschiedlichen Stellen im Data-Dictionary gefunden werden. In den folgenden Abfragen werden einige USER-Dictionary Views gezeigt, die korrespondierenden ALL- und DBA-Views beinhalten die gleichen Informationen. Das erste Beispiel zeigt die Spalten der Testtabelle mit dem Wert DATA_DEFAULT.
SQL> SELECT column_name
2 , data_type
3 , data_default
4 FROM user_tab_columns
5 WHERE table_name = ‘T_TEST’
6 ORDER BY
7 column_id;

COLUMN_NAME DATA_TYPE DATA_DEFAULT
———– ——— —————————————
C_COL1 NUMBER
C_COL2 NUMBER
C_COL3 NUMBER “C_COL1″+”C_COL2”
C_COL4 NUMBER “C_COL1″*”C_COL2”
C_COL5 NUMBER “VTEST”.”F_SUMMIERE”(“C_COL1″,”C_COL2”)

SQL>

Die Spalte DATA_DEFAULT zeigt hier also die Definition der Ausdrücke, die die virtuellen Spalten beschreiben. Anhand der Data-Dictionary View USER_TAB_COLUMNS kann man zwischen physisch vorhandenen und virtuellen Spalten unterscheiden.
SQL> SELECT column_name
2 , virtual_column
3 , segment_column_id
4 , internal_column_id
5 FROM user_tab_cols
6 WHERE table_name = ‘T_TEST’
7 ORDER BY
8 column_id;

COLUMN_NAME VIRTUAL_COLUMN SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
———– ————– —————– ——————
C_COL1 NO 1 1
C_COL2 NO 2 2
C_COL3 YES 3
C_COL4 YES 4
C_COL5 YES 5

SQL>

Der oben angelegte Index kann durch Abfrage der View USER_INDEXES abgefragt werden. Der INDEX_TYPE zeigt, dass es sich – wie oben gesagt – um einen function-based Index auf der virtuellen Spalte handelt. Weder die virtuellen Spalten, noch die Ausdrücke des function-based Index werden physisch an einem anderen Ort abgelegt als im Index selbst. Die Data-Dictionary View USER_IND_EXPRESSIONS zeigt Informationen über den Primärschlüsselindex auf der virtuellen Spalte c_col3.
SQL> SELECT index_name
2 , index_type
3 , funcidx_status
4 FROM user_indexes
5 WHERE table_name = ‘T_TEST’;

INDEX_NAME INDEX_TYPE FUNCIDX_STATUS
———- ————————— ————–
T_TEST_PK FUNCTION-BASED NORMAL ENABLED

SQL> SELECT *
2 FROM user_ind_expressions
3 WHERE index_name = ‘T_TEST_PK’;

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
———- ———- —————– —————
T_TEST_PK T_TEST “C_COL1″+”C_COL2” 1

SQL>

Diese Informationen im Data-Dictionary sind für jeden DBA wichtig. Es sei an dieser Stelle bemerkt, dass andere Data-Dictionary Views (z. B. USER_CONS_COLUMNS, USER_CONSTRAINT, USER_COL_COMMENTS, …) nicht zwischen physisch vorhandenen und virtuellen Spalten unterscheiden.

Virtuelle Spalten, Speicherplatz und der Cost Based Optimizer (CBO)

Wie bereits oben ausgeführt, belegen virtuelle Spalten selbst keinen Speicherplatz. Funktionen wie DUMP oder VSIZE liefern hingegen trotzdem die “normalen” Werte zurück, denn die Funktionen arbeiten auf dem Ergebnis des Ausdrucks, der der virtuellen Spalte zugrunde liegt. Dieses Verhalten sieht man im folgenden Beispiel.
SQL> SELECT DUMP(c_col3) AS dump
2 , VSIZE(c_col3) AS vsize
3 FROM t_test
4 WHERE ROWNUM = 1;

DUMP VSIZE
———————————– ——-
Typ=2 Len=2: 193,12 2

SQL>

Die Tatsache, dass auch mehrere virtuelle Spalten keinen zusätzlichen Platz verbrauchen, läßt sich an dem folgenden Beispiel leicht beweisen. Es werden zwei Tabellen angelegt und mit jeweils 10.000 Datensätzen gefüllt.
SQL> CREATE TABLE t_storage_test
2 ( c_col1 VARCHAR2(4000)
3 , c_col2 VARCHAR2(4000)
4 , c_col3 VARCHAR2(4000)
5 )
6 PCTFREE 0;

Table created.

SQL> CREATE TABLE t_storage_test_vc
2 ( c_col1 VARCHAR2(4000)
3 , c_col2 VARCHAR2(4000)
4 , c_col3 VARCHAR2(4000)
5 , c_col4 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col1)) VIRTUAL
6 , c_col5 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col2)) VIRTUAL
7 , c_col6 VARCHAR2(4000) GENERATED ALWAYS AS (UPPER(c_col3)) VIRTUAL
8 )
9 PCTFREE 0;

Table created.

SQL> INSERT INTO t_storage_test
2 SELECT RPAD(‘x’,4000)
3 , RPAD(‘x’,4000)
4 , RPAD(‘x’,4000)
5 FROM dual
6 CONNECT BY ROWNUM INSERT INTO t_storage_test_vc (c_col1, c_col2, c_col3)
2 SELECT RPAD(‘x’,4000)
3 , RPAD(‘x’,4000)
4 , RPAD(‘x’,4000)
5 FROM dual
6 CONNECT BY ROWNUM

Jetzt lassen sich beide Tabellen über eine einfache Data-Dictionary Abfrage vergleichen.
SQL> SELECT segment_name
2 , bytes
3 , blocks
4 , extents
5 FROM user_segments
6 WHERE segment_name LIKE ‘T_STORAGE_TEST%’;

SEGMENT_NAME BYTES BLOCKS EXTENTS
————————- ———- ———- ———-
T_STORAGE_TEST 125829120 15360 86
T_STORAGE_TEST_VC 125829120 15360 86

SQL>

Wie erwartet, ist der Bedarf an Speicherplatz für beide Tabellen identisch.

Der Cost Based Optimizer (CBO) behandelt physisch vorhandene und virtuelle Spalten gleich, d. h. es wird auf Basis identischer Annahmen kalkuliert, wenn entsprechende Statistiken fehlen. Durch Aktivierung des AUTOTRACE Feature kann man die Ausführungspläne der Abfragen auf der soeben angelegten Tabelle t_storage_test_vc sehen. Um den Ausführungsplan lesbarer zu gestalten, wird als erstes ein Update durchgeführt. Anschließend erfolgt die erste Beispielabfrage.
SQL> UPDATE t_storage_test_vc SET c_col1 = ‘X’;

10000 rows updated.

SQL> COMMIT;

Commit complete.

SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_storage_test_vc WHERE c_col1 = ‘X’;

Execution Plan
———————————————————-
Plan hash value: 1169272982

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 11337 | 129M| 5443 (25)| 00:01:06 |
|* 1 | TABLE ACCESS FULL| T_STORAGE_TEST_VC | 11337 | 129M| 5443 (25)| 00:01:06 |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“C_COL1″=’X’)

Note
—–
– dynamic sampling used for this statement (level=2)

SQL>

Da keine Statistiken vorhanden sind, wird dynamic sampling verwendet. Der CBO nimmt an, dass nahezu alle Sätze der Tabelle die Abfragekriterien erfüllen. Die zu c_col1 korrespondierende Spalte ist c_col4 und wird über den Ausdruck UPPER(c_col1) erzeugt. Die Abfrage ergibt folgendes Bild.
SQL> SELECT * FROM t_storage_test_vc WHERE c_col4 = ‘X’;

Execution Plan
———————————————————-
Plan hash value: 1169272982

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 113 | 1325K| 5452 (25)| 00:01:06 |
|* 1 | TABLE ACCESS FULL| T_STORAGE_TEST_VC | 113 | 1325K| 5452 (25)| 00:01:06 |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“C_COL4″=’X’)

Note
—–
– dynamic sampling used for this statement (level=2)

Der CBO ermittelt eine andere Kardinalität für die virtuelle Spalte, ungeachtet der Tatsache, dass c_col4 sich direkt aus c_col1 ergibt. Offenbar legt der CBO die Standard-Selektivität von 1% für einen Ausdruck der Form “function(column)=literal” an, um zu diesem Ergebnis zu kommen. Interessant ist, dass auch hier dynamic sampling zugrunde gelegt wird. Das bedeutet, dass auch auf virtuellen Spalten Statistiken berechnet werden können. Dieses wiederum hat zur Folge, dass der CBO mit Statistiken auf Spaltenebene und natürlich mit Histogrammen eine wesentlich genauere Abschätzung der Kosten des Statements vornehmen kann. Also werden nun Statistiken berechnet und die Abfrage auf die virtuelle Spalte c_col4 wird erneut ausgeführt.
SQL> set autotrace off
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(USER, ‘T_STORAGE_TEST_VC’);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_storage_test_vc WHERE c_col4 = ‘X’;

Execution Plan
———————————————————-
Plan hash value: 1169272982

—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 10000 | 152M| 5452 (25)| 00:01:06 |
|* 1 | TABLE ACCESS FULL| T_STORAGE_TEST_VC | 10000 | 152M| 5452 (25)| 00:01:06 |
—————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“C_COL4″=’X’)

SQL>

Man sieht auf den ersten Blick, dass nun die korrekte Kardinalität zugrunde gelegt wird. Dieses läßt sich anhand weiterer Abfragen auf Tabellen- und Spaltenebene verifizieren.
SQL> SELECT num_rows
2 , sample_size
3 FROM user_tab_statistics
4 WHERE table_name = ‘T_STORAGE_TEST_VC’;

NUM_ROWS SAMPLE_SIZE
———- ———–
10000 10000

SQL> SELECT column_name
2 , num_distinct
3 , density
4 , num_nulls
5 , num_buckets
6 , histogram
7 FROM user_tab_col_statistics
8 WHERE table_name = ‘T_STORAGE_TEST_VC’
9 ORDER BY
10 column_name;

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS HISTOGRAM
———– ———— ———- ———- ———– —————
C_COL1 1 .00005 0 1 FREQUENCY
C_COL2 1 1 0 1 NONE
C_COL3 1 1 0 1 NONE
C_COL4 1 .00005 0 1 FREQUENCY
C_COL5 1 1 0 1 NONE
C_COL6 1 1 0 1 NONE

6 rows selected.

SQL>

Die Statistiken scheinen wesentlich besser zu sein. Die Spalte DENSITY besitzt für die Spalten c_col1 und c_col4 andere Werte, da Histogramme angelegt worden sind. Die Histogramme wurden automatisch erzeugt, da diese Spalten in den Abfragen vor Berechnung der Statistiken verwendet worden sind. Die Verwendung aller in Prädikaten verwendeten Spalten wird in den neueren Versionen von Oracle durchgeführt, um das Berechnen der Statistiken noch präziser automatisiert ausführen zu können.

Virtuelle Spalten und Partitionierung

Oben wurde ausgeführt, dass virtuelle Spalten in Primärschlüsseln verwendet werden können. Ebenso können sie seit Oracle 11g als Partitionsschlüssel in partitionierten Tabellen verwendet werden. Dieses funktioniert auch für sub-partitionierte Tabellen, in denen die virtuelle Spalte sowohl in der Partition als auch in der Subpartition als Schlüssel verwendet wird. Die Funktionsweise soll an einem einfachen Beispiel gezeigt werden, das auf den Daten der Data-Dictionary View ALL_OBJECTS beruht. Die Spalte c_col_p1 ist hier eine virtuelle Spalte, deren Wert auf Basis der Spalten c_col_c1 und c_col_c2 berechnet wird.
SQL> CREATE TABLE t_part_test
2 ( c_col_n1 INTEGER
3 , c_col_c1 VARCHAR2(30)
4 , c_col_c2 VARCHAR2(30)
5 , c_col_d1 DATE NOT NULL
6 , c_col_p1 VARCHAR2(61)
7 GENERATED ALWAYS
8 AS (c_col_c1 || ‘_’ || CASE
9 WHEN c_col_c2 LIKE ‘TABLE%’
10 THEN ‘TABLE’
11 WHEN c_col_c2 LIKE ‘INDEX%’
12 THEN ‘INDEX’
13 WHEN c_col_c2 LIKE ‘PACKAGE%’
14 OR c_col_c2 LIKE ‘TYPE%’
15 OR c_col_c2 IN (‘TRIGGER’,’PROCEDURE’,’FUNCTION’)
16 THEN ‘PLSQL’
17 ELSE ‘OTHER’
18 END)
19 )
20 PARTITION BY LIST (c_col_p1)
21 ( PARTITION p_scott_table VALUES (‘SCOTT_TABLE’)
22 , PARTITION p_scott_index VALUES (‘SCOTT_INDEX’)
23 , PARTITION p_scott_plsql VALUES (‘SCOTT_PLSQL’)
24 , PARTITION p_scott_other VALUES (‘SCOTT_OTHER’)
25 , PARTITION p_sh_table VALUES (‘SH_TABLE’)
26 , PARTITION p_sh_index VALUES (‘SH_INDEX’)
27 , PARTITION p_sh_plsql VALUES (‘SH_PLSQL’)
28 , PARTITION p_sh_other VALUES (‘SH_OTHER’)
29 );

Table created.

SQL>

Nachdem die partitinierte Tabelle mit 8 Partitionen erstellt worden ist, wird diese nun mit Daten versorgt. Eine kleine Abfrage zeigt die Datenverteilung. Zum Abschluß der Vorbereitungen werden die Statistiken auf der partitionierten Tabelle berechnet.
SQL> INSERT INTO t_part_test (c_col_n1, c_col_c1, c_col_c2, c_col_d1)
2 SELECT object_id
3 , owner
4 , object_type
5 , created
6 FROM dba_objects
7 WHERE owner IN (‘SCOTT’,’SH’);

312 rows created.

SQL> SELECT c_col_p1, COUNT(*)
2 FROM t_part_test
3 WHERE c_col_c1 = ‘SH’
4 GROUP BY
5 c_col_p1;

C_COL_P1 COUNT(*)
————————————————————- ———-
SH_TABLE 73
SH_INDEX 223
SH_OTHER 10

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(user,’T_PART_TEST’);
3 END;
4 /

PL/SQL procedure successfully completed.

SQL>

Die folgenden Abfragen sollen nun zeigen, wie auch bei der Partitionierung mit virtuellen Spalten eine Partition Elimination funktioniert. Über das AUTOTRACE Feature wird der Ausführungsplan erzeugt.
SQL> set autotrace traceonly explain
SQL> SELECT * FROM t_part_test WHERE c_col_p1 = ‘SH_INDEX’;

Execution Plan
———————————————————-
Plan hash value: 2593459040

—————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
—————————————————————————————————–
| 0 | SELECT STATEMENT | | 223 | 8920 | 7 (58)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 223 | 8920 | 7 (58)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS FULL | T_PART_TEST | 223 | 8920 | 7 (58)| 00:00:01 | 6 | 6 |
—————————————————————————————————–

SQL>

Man sieht, dass durch den Equi-Join auf dem Partitionsschlüssel wie erwartet alle Partitionen elimiert werden, außer der Partition, die die gewünschten Daten enthält. Das folgende Beispiel zeigt einen LIKE-Ausdruck auf dem Partitionierungsschlüssel:
SQL> SELECT * FROM t_part_test WHERE c_col_p1 LIKE ‘SH%’;

Execution Plan
———————————————————-
Plan hash value: 509857162

——————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
——————————————————————————————————-
| 0 | SELECT STATEMENT | | 62 | 2418 | 11 (46)| 00:00:01 | | |
| 1 | PARTITION LIST ITERATOR| | 62 | 2418 | 11 (46)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | T_PART_TEST | 62 | 2418 | 11 (46)| 00:00:01 | KEY | KEY |
——————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – filter(“C_COL_P1” LIKE ‘SH%’)

SQL>

Man sieht, dass in diesem Beispiel ein PARTITION LIST ITERATOR für den LIKE-Ausdruck gewählt wird. Die zu lesende Partition wird also erst zur Laufzeit ermittelt. Dieses funktiniert natürlich nur für LIKE-Ausdrücke, die KEINE führenden Wildcards besitzen.

Einschränkungen bei der Nutzung von Virtuellen Spalten

Wie man aus den obigen Ausführungen und Beispielen entnehmen konnte, sind virtuelle Spalten ein sehr mächtiges Werkzeug. Es gibt jedoch noch einge Einschränkungen, die bei der Benutzung virtueller Spalten beachtet werden müssen:
Virtuelle Spalten können nur in sogenannten Heap-organisierten Tabellen verwendet werden. Virtuelle Spalten in index-organized, externen, Objekt-, Cluster- oder temporären Tabellen sind nicht unterstützt.
Der Ausdruck in der AS-Klausel der Defintion der virtuellen Spalte hat folgende Einschränkungen:
Der Ausdruck darf sich nicht auf eine andere virtuelle Spalte beziehen.
Jede Spalte, auf die sich die virtuelle Spalte bezieht, muss sich in der gleichen Tabelle befinden.
Eine deterministische benutzerdefinierte Funktion kann verwendet werden. In diesem Fall kann die virtuelle Spalte jedoch NICHT als Partitionierungsschlüssel genutzt werden.
Das Ergebnis des Ausdruckes muss ein skalarer Wert sein.
Eine virtuelle Spalte darf kein Oracle-supplied Datentyp (z. B. LCR-Typ) sein, ebenso kein benutzer-definierter Typ, LOB oder LONG RAW.

Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben …

Zurück zur Community-Seite

The Tiny, Essential Google Tricks for Way Better Search Results

The Tiny, Essential Google Tricks for Way Better Search Results

The Tiny, Essential Google Tricks for Way Better Search Results

Tegan Jones
Today 10:28am
Filed to:GOOGLE

Screenshot: Lifehacker
Are you using Google effectively as possible? If you’re just entering words into the search field without using these totally basic but totally essential tricks to improve your results, you’re missing out. We like to think of ourselves as Google ninjas at Lifehacker, but even we need a reminder of these crucial shortcuts now and then.

Dashes
If you want to exclude a word from your search results, put a dash in front of it.

Example:

Watch West Wing online -Netflix

Quotation Marks
Use quotation marks to search an exact set of words, such as song lyrics.

Example:

“You must remember this” song

Asterisk
Speaking of exact words, what if you can’t remember them all? No problem—just use an asterisk in place of the unknown word/s. Again, this is great for song lyrics or quotes that you may have only half heard. Alternatively, ones that are often misquoted, like below.

Example:

“Play * Sam”

Tilde
Use a tilde before a word to include all of its synonyms.

Example:

Star Wars ~Presents

As you can see, it has scraped ‘gifts’ as well:

Double Period
Use a double period between two numbers to convey ranges. This is handy for pricing, dates and measurements.

Example:

HP Spectre buy $1000..$2000

Site: Query
You can search for something within a specific website by using ‘site:’.

Example:

How I Work site:lifehacker.com

Link: Query
You can find sites that have linked to a specific URL through ‘link:’

Example: We wanted to find sites that linked to this Lifehacker post about teens and juuling.

Related: Query
If you’re looking for websites that are related to a specific site, you can use ‘related:’

Example:

related:boardgamegeek.com

Reverse Image Search
This is incredibly handy if you want to find the origin of a photo you have randomly stumbled across on the web. For example, a plate of delicious looking food that you would love to know the recipe for.

Reverse image searching is also great for tracking down original photographers, identifying things (celebrities, flora and fauna, unlabelled clothes or products you want to buy), discovering where your own work may be getting used, and debunking fake social media posts and profiles.

You can do a reverse image search by going into the ‘images’ tab on Google and clicking on the camera icon in the search bar. You can then either upload an image or insert an image address (right click on an image and hit ‘copy image address). Google will then deliver its best guess on the image.

Example:

I went to Pinterest, searched ‘Ramen’ and chose this image:

I then reverse image searched it on Google to find the recipe.

If you’ve got search tips that everyone should know about, tell us in the comments.

This post originally appeared on Lifehacker Australia.

E-Trikes

E-Trikes

VELOKSMore than a trike Navigation
Our company
VELOKS is a new company located in Denmark. We design, fabricate and sell electric trikes and e-bike parts.

Get in touch

Placeholder
Our e-Trikes
Designed for long-range electric assisted trips on any surface and under any condition. The extreme low center of gravity makes the trikes stable at any speed. The full suspension and large fat tires, along with the comfortable padded seat, makes riding on all surfaces a true joy.

Buy trike

Placeholder
Our batteries
We produce our batteries from 18650 Panasonic lithium cells. Our batteries contains beteen 96 and 320 cells, and has built-in management system (BMS) and Motor controller. Our batteries will last between 500 – 2000 charge cycles depending upon how they are charged. Our batteries are shock and water-resistant.

Placeholder
Trike riding is living
Riding on a VELOKS e-Trike is pure enjoyment. The fully suspended chassis together with the fat tires, and the padded seat, allows you to sit comfortably for hours and hours without any discomfort. The powerfull and easy to control electrical drive system will ensure that you arrive at your destination relaxed and ready to enjoy the afternoon and evening.

Key features
Comfortable ride
Fully suspended, fat tires, padded recumbent seat, relaxed riding postion

Designed for you
Go effortless, anywhere lying down

Powerfull drive system
Assisted range from 160 to 640 km. Designed for 60 km/h, restricted to 25 km/h

placeholder
Want to know more ?

0
VOLT
0
WATTS
0
KM/H
0
KM RANGE
HOME

CHECKOUT

CART

CONTACT

NEWSLETTER

FAQ

T&C

PRIVACY POLICY
Translate

Execute vs Read bit. How do directory permissions in Linux work?

Execute vs Read bit. How do directory permissions in Linux work?

When applying permissions to directories on Linux, the permission bits have different meanings than on regular files.

The read bit allows the affected user to list the files within the directory
The write bit allows the affected user to create, rename, or delete files within the directory, and modify the directory’s attributes
The execute bit allows the affected user to enter the directory, and access files and directories inside
The sticky bit states that files and directories within that directory may only be deleted or renamed by their owner (or root)

– – –

First, think: What is a directory? It’s just a list of items (files and other directories) that live within. So: directory = list of names.

Read bit = If set, you can read this list. So, for example, if you have a directory named poems:

You can ls poems and you’ll get a list of items living within (-l won’t reveal any details!).
You can use command-line completion i.e. touch poems/so poems/somefile.
You cannot make poems your working directory (i.e. cd into it).
Write bit = If set, you can modify this list i.e. you can {add,rename,delete} names on it. But! You can actually do it only if the execute bit is set too.

Execute bit = Make this directory your working directory i.e. cd into it. You need this permission if you want to:

access (read, write, execute) items living within.
modify the list itself i.e. add, rename, delete names on it (of course the write bit must be set on the directory).
Interesting case 1: If you have write + execute permissions on a directory, you can {delete,rename} items living within even if you don’t have write perimission on those items. (use sticky bit to prevent this)

Interesting case 2: If you have execute (but not write) permission on a directory AND you have write permission on a file living within, you cannot delete the file (because it involves removing it from the list). However, you can erase its contents e.g. if it’s a text file you can use vi to open it and delete everything. The file will still be there, but it will be empty.

Summary:

Read bit = You can read the names on the list.
Write bit = You can {add,rename,delete} names on the list IF the execute bit is set too.
Execute bit = You can make this directory your working directory.

What is Trunk-Based Development?

What is Trunk-Based Development?

It is a branching model for software development. Historically, it has also been called “mainline” (see later).

It requires much more concentration and rigor, than making a branch (on the shared source-control server) to suit a whim. Though you could do it without Continuous Integration (CI), as many open source projects do, for enterprise development you have to have CI linked to the trunk, enforcing multiple aspects of “that commit was good”.

In this article, I’m saying nothing about what developers do on their own workstations by way of ‘local’ branching to suit their hour by hour activities. This is all about the shared repo, where multiple developers integrate/merge their daily work for the greater good 🙂

Trunk-Based Development (TBD) is where all developers (for a particular deployable unit) commit to one shared branch under source-control. That branch is going to be colloquially known as trunk, perhaps even named “trunk”. Devs may, on their own dev workstations, do some multi-branch development (say with Git), but when they are “done” with a change or a bug fix, it should go back to the shared trunk. It is not “done” if it is not there – watch for that little lie of omission. See the section about pull-requests below, too.

Branches are made for a release. Developers are not allowed to make branches in that shared place. Only release engineers commit to those branches, and indeed create each release branch. They may also cherry-pick individual commits to that branch if there is a desire to do so. After a release has been superseded by another, the branch is most likely deleted.

Trunk as a model, has been in use for twenty years or so. Initially promoted by the open-source community, but less so in “enterprise-land” when ClearCase (and others) published other branching models that became dominant. Google and Facebook, today, practice a TBD style branching model. If not exactly, then close enough. Either with Google and Facebook publicizing their TBD usage, the mindshare is growing for it.

But there are branches!
Yes there are, but concerning releases. “Branch on/for Release” is the strategy. The release branch that will live for a short time before it is replaced by another release branch, takes everything from trunk when it is created. In terms of merges, only cherry-picks FROM trunk TO the release branch are supported. For many enterprises, only bug fixes will be merged. For Facebook (who go live from release branches ten times a week), merges of enhancements happen too, if prioritized by stakeholders.

Nearly everyone agrees that bugs are fixed on the trunk and merged to the release branch, instead of fixed on the release branch and merged to trunk. There is usually a reduced set of people that can commit to the release branch.

Pull-Requests are still branches!
OK, so GitHub pioneered the pull-request as a development workflow. This is quite compatible with TBD, in that a feature/task is marshaled in a place that is not yet on the trunk/master but can be quickly. Normally, code review happens there and CI weighs in automatically with an opinion as to whether the PR branch is eligible to be merged into the trunk/master or not. It everything is right, the the PR is merged back in the master/trunk and then deleted, leaving a smooth trunk/master timeline. Of course, the feature/task branch that’s subject of a pull-request later should be a one-person or one-pair branch, and very short lived (say a day). There are variations too – both ‘forks’ and simple branches of the origin are good pull-requests choices. Only repository read/write permissions guide which should be used.

Obligations for developers
Developers do not break the build with any commit. This requires a lot of discipline, and perhaps why the induction programs of Google and Facebook are lengthy for developers. Rollback/revert of a commit is a strategy to prevent the damage (lost time) from that. More sophisticated companies will use pre-commit verifications. Devs take on habit: prove the commit is good, by synchronizing to the the trunk’s latest revisions, building from root/scratch, double-checking their functional change, then committing. In the early days, including in ThoughtWorks, devs had a “token” to prove that they had not broken the build – nobody else could hold the chicken while they were going through that proving cycle. Rubber Chickens have been used for over a decade for this, but anything will do (thanks to Jez for the link).

Continuous Integration
Continuous Integration, like Jenkins, kicks in for that commit, and runs through a build pipeline building, testing, deploying, testing some more. It may detect failures, and most likely that is because a developer didn’t prove their commit or do the token thing. Another issue might be the developer failed to add a new source file before a commit. This would be easy/quick to remedy, and a situation where “roll forward” would be OK.

Changes that take “too long” to complete
Developers use a technique called Branch by Abstraction (BbA) to ensure that they can complete trickier changes over a longer timespan. I’ve written about that many times. Martin Fowler underlines it as important, and Jez Humble has written about it too. The risk it mitigates, is of the proliferation of branches, and those ‘temporary’ branches not completing on a schedule that has been hoped for.

My own case studies
From 2005, a move from crazy branching towards TBD (a US FX trading bank).

Trunk-Based Development recap
Quick reminder of what TBD is:

Developers commit to a single trunk more or less exclusively
Release engineers (or build-cop) create branches, and cherry-pick to branches more or less exclusively
Only if a defect cannot be reproduced on trunk, is permission given to fix it on the release branch, and cherry-pick back to trunk.
And if you get the release branch concept, it’s worth remembering:

Trunk-Based Development means regular developers don’t commit to a release branch.
Trunk-Based Development means you’re going to delete ‘old’ release branches, without merging them back to trunk.
What is definitely not TBD
Multiple branches that developers commit to
Branches containing the same source files, that is. Refer BbA above – you should be doing it. Often senior devs would claim they have a special case, and want to do it on a branch. The pitfall is the proliferation of branches on the shared source-control server, the length of their ‘temporary’ life, and the difficulty of merging when there are lots of developers and lots of commits to one place or another.

This one aspect is debated back and forth, even by people that like the concept of a trunk. I’m going to put a line in the sand, and say that you should not make branches (on that shared repo) for features regardless of how long they are going to take, and whether they run over release dates. You should do BbA instead.

Not doing a CI pipeline on that single branch
Sure as a personal practice, you could prevent breakage, and many open source teams will argues they are good without CI. But for enterprise-land with tens of developers you need thorough CI.

Manual version number maintenance for dependencies
Dependencies of components for a buildable using can be expressed in a versioned way.

For example Log4J is currently at 1.2.17, and maintained by Apache. You’re not going to pull in their source to your source-control. You’ll depend on a binary, and bake the version number for your own build files (under source control).

For your own stuff, that’s perhaps been built in a different phase of CI, your should not bake in version numbers for specific builds. Borrowing from Maven’s idioms you should instead depend on a moving target under CI. Say ‘OurCommonThings-1.1-SNAPSHOT’, but ensure you build that in a ‘correct’ CI build phase. You’ve no intention of going live like that, but you can’t be Continuously integrating if your not compiling against, and testing with the latest version of everything.

A more hardcore implementation in CI pipeline would use something other than the a controversial ‘1.1-SNAPSHOT’ I suggested above. It (and Maven per se) is a controversial thing in enterprise development. For Subversion or Perforce installations, the repository revision number could be what you use instead – ‘1.1-12345’. Alternatively, a build number might be popular (all CI tools can provide a build number to the scripts they execute).

Not doing CI from ‘root’
In regular configurations, CI should build all your stuff from root/scratch, and not depend on anything built in a prior run. Some of the more sophisticated CI infrastructures (like ThoughtWorks Studios’ Go), have a more provable/fingerprinted way of tactically using pre-build pieces, but regular installations should build from scratch (as fast as possible).

This is another variation of the “latest version of everything” goal.

Concurrent Development of Consecutive Releases
Some enterprises work on a series of releases at the same time. They are intending to do dark-deployments using runtime toggles, but also perhaps have build-time switches to subset functionality in the resulting binary, depending on what they want to test in the CI phase. They may have more than one CI pipelines setup for the same trunk, that proves that the “amazon_one_click=true” and the “amazon_one_click=false” alternates build and pass tests. Either one of the two failing is still failure for a commit, and subject to rollback/revert.

You’d only set up pipelines with differing permutations of toggles for releases you’re expecting to put live. If ‘management’ cancel the features of one release (activated by a single toggle), or reorder releases, then you reconfigure the CI pipelines as soon as you can. The dollar and time impact of that ‘re-planning’ is clarified soonest by CI, and the resulting passing/failing view of the trunk for each. You’re never going to test unreasonable toggle permutations.

Incidentally the eXtreme Programming community correctly suggests that consecutive development of consecutive releases is preferable.

Misnomers
Mainline is something else
OK, so classically “Mainline” is a synonym of trunk, and for trunk-based-development people have been using “mainline” to describe that too. The trouble is that “mainline” also used by the ClearCase community from 1993 and refers to a wasteful and delaying branching model likes so:

This is also a “late” integration design, whereas TBD is a “earliest” integrations, which is one of the critical concepts, and greatest facilitator of cost-reduction during development. The other reality of this branching model, is branches that hang off the release branch, that are supposed to be temporary.

So, in summary, mainline means something else to a lot of software developers.

Feature Toggles
I’ve recently heard people refer to TBD as “Feature Toggles”. Martin Fowler named this long-held practice for the industry. It is often used in conjunction with TBD, but does not have to be. It can be used with any branching pattern, and is perhaps as old as developing software services and putting them live.

At a previous client we talked of build-time toggles too. These, for maven, were profiles like so:

# with amazon one click
mvn -p amazon_one_click install

# without amazon one click
mvn install
Thus for that client, toggles at runtime were different to toggles that were at build time (Maven profiles), but some could be both of course. As mentioned previously, CI pipelines are going to kicking on commit for reasonable toggle permutations.

Continuous Delivery (and Deployment)
This is the step up from simple TBD and CI usage. Jez has a well known book Continuous Delivery that is essential reading.

Thanks to…
Jez Humble for errata, and a nice quote “branching is not the problem, merging is the problem” (that’s a way of stating one problem TBD is trying to solve)

Updates
June 30th, 2016 – We smile on Pull Requests, of course.

6 Github alternatives that are open source and self-hosted

6 Github alternatives that are open source and self-hosted

Skip to content

nixCraft
Search MENU
6 Github alternatives that is open source and self-hosted
Posted on June 4, 2018in Categories Open Source last updated June 4, 2018
Git is a version control system. Linus Torvalds created Git in 2005 for development of the Linux kernel. Git is mainly used for software source code management. However, it can be used to keep track of any set of files such as HTML or CSS or a tutorial written in the markup language. Git is a Distributed Version Control Systems. A dedicated Git server helps to manage access control, show contents of a Git repository via the web and manage multiple repositories. In a Git, clients do not just check out the latest snapshots of the files. They entirely mirror the repository including history.

Microsoft to acquire Github
It is official now. Microsoft announced an agreement to acquire GitHub, the world’s leading software development platform. I think it is a wake-up call for open source projects. So much of open source code hosted on Github. I twitted this other day:

Some of us working in IT for a long time. We have not forgotten big evil Microsoft. Microsoft is the same company that was behind the SCO Lawsuit against Linux and called open source cancer. It actively follows a policy of Embrace, Extend, Extinguish. Microsoft is also known for its abuse of patents (see Netscape and Microsoft is generating $2 billion per year in revenue from Android patent royalties) and the recent Windows 10 Telemetry privacy issues. The list is endless. One should be a cautious trusting company that has a strong history of abuse towards open source communities and Linux operating system. Hence, installing your Git server is the best policy.
Open Source GitHub Alternatives

Various implementations of Git and Github alternatives
There is a number of implementations of Git server:

Proprietory (e.g. Bitbucket)
Git as service (e.g. GitHub)
Open source (e.g. gitolite, Gerrit)
Let us see six open source Github alternatives that you can host on your own server.

1. GitLab – a web-based Git-repository manager with wiki, issue-tracking features and more
GitLab is a free, open source and a web-based Git-repository manager software. It has a wiki, issue tracking, and other features. The code was originally written in Ruby, with some parts later rewritten in Golang. GitLab Community Edition (CE) is an open source end-to-end software development platform with built-in version control, issue tracking, code review, CI/CD, and more. Self-host GitLab CE on your own servers, in a container, or on a cloud provider.

GitLab is Github alternative self hosted open source software
Manager: GitLab B.V. Netherlands.
Server side free software: Partial [GitLab Community Edition (CE)]
Client side free software: Yes
Code review: Yes
Bug tracking: Yes
Web hosting: Yes
Wiki: Yes
Private branch: Yes
Personal branch: Yes
Build system: Yes
Release binary: Yes
Self hosting: Yes
Version control system: Git
License: MIT Expat
Commercial support: Yes
Operating system/stack support: Linux (Ubuntu/Debian/CentOS/RHEL/OpenSUSE) + Ruby, Redis, PostgreSQL/MySQL, and Git
Download: https://gitlab.com/gitlab-org/gitlab-ce
2. Gitea – Git with a cup of tea
It is a painless self-hosted Git service. Gitea is a community fork of Gogs software. It is lightweight code hosting solution written in Golang and released under the MIT license. It works on Windows, macOS, Linux, ARM and more.

Gita Github alternatives open source and self hosted software
Manager: The Gitea Authors.
Server side free software: Yes
Client side free software: Yes
Code review: Yes
Bug tracking: Yes
Web hosting: Yes
Wiki: Yes
Private branch: Yes
Personal branch: Yes
Build system: No
Release binary: No
Self hosting: Yes
Version control system: Git
License: MIT
Commercial support: N/A
Operating system/stack support: Linux/Unix/Windows (anything that can run Golang), PostgreSQL/MySQL database
Download: https://gitea.io
3. GNU Savannah
GNU Savannah is a free and open source software from the Free Software Foundation. It currently offers CVS, GNU arch, Subversion, Git, Mercurial, Bazaar, mailing list, web hosting, file hosting, and bug tracking services. However, this software is not for new users. It takes a little time to setup and masters everything about it.

GNU Savannah Self Hosted Github alternatives
Manager: Free Software Foundation, Inc. .
Server side free software: Yes
Client side free software: Yes
Code review: Yes
Bug tracking: Yes
Web hosting: Yes
Wiki: No
Private branch: No
Personal branch: No
Build system: No
Release binary: N/A
Self hosting: Yes
Version control system: Git, CVS, HG, SVN, BZR
License: MIT Expat
Commercial support: Yes
Operating system/stack support: Linux (Ubuntu/Debian/CentOS/RHEL/OpenSUSE) + Ruby, Redis, PostgreSQL/MySQL, and Git
Download: http://savannah.gnu.org/projects/administration
4. GitBucket – Open source GitHub clone written with Scala
Gitbucket is a Git platform powered by Scala with easy installation, high extensibility & GitHub API compatibility. It provides GitHub like user interface (UI) and features such as Git repository hosting via HTTP/HTTPS and SSH, repository viewer, issues, wiki and pull request.

GitBicket Github Alternatives Open Source Software
Manager: The Gogs Authors.
Server side free software: Yes
Client side free software: Yes
Code review: Unknown
Bug tracking: Unknown
Web hosting: Yes
Wiki: Yes
Private branch: Yes
Personal branch: Unknown
Build system: No
Release binary: No
Self hosting: Yes
Version control system: Git
License: Apache License 2.0
Commercial support: Unknown
Operating system/stack support: Java 8 (Linux/Unix/Windows)
Download: https://github.com/gitbucket/gitbucket
5. Gogs – A painless self-hosted Git service
The goal of this project is to make the easiest, fastest, and most painless way of setting up a self-hosted Git service. With Go, this can be done with an independent binary distribution across ALL platforms that Go supports, including Linux, Mac OS X, Windows and ARM.

Googs Github Alternatives Open Source Software
Manager: The Gogs Authors.
Server side free software: Yes
Client side free software: Yes
Code review: Yes
Bug tracking: Yes
Web hosting: Yes
Wiki: Yes
Private branch: Yes
Personal branch: Yes
Build system: No
Release binary: No
Self hosting: Yes
Version control system: Git
License: MIT
Commercial support: N/A
Operating system/stack support: Linux/Unix/Windows (anything that can run Golang), MySQL/PostgreSQL/MSSQL/TiDB database
Download: https://gogs.io/
6. Other Github alternatives options
Here are few more alternatives to GitHub for all platforms with Open Source License:

GitPrep
It is Github clone. You can install portable github system into unix/linux. You can create users and repositories without limitation. This is free software.

Kallithea
Kallithea, a member project of Software Freedom Conservancy, is a GPLv3’d, Free Software source code management system that supports two leading version control systems, Mercurial and Git, and has a web interface that is easy to use for users and admins. You can install Kallithea on your own server and host repositories for the version control system of your choice.

TuleapL
Tuleap is a project management system for managing application lifecycles, Agile development and design projects, V-model, Requirement Management, IT Services Management, and so on. Tuleap integrates forge system functionalities that enables teams to manage software sources (using Subversion, Git or CVS); share technical or project documentations; track bugs; consolidate communications with customers, developers or third parties.

Phabricator
Phabricator is a collection of web applications which help software companies build better software. It is a set of tools for developing software. It includes applications for code review, repository hosting, git, bug tracking, project management, and more.

Conclusion
And there you have it, six Github alternatives that you can host on your Linux or Unix server. No need to depend on the cloud or worry about Microsoft looking into your codebase. If I miss any other well know the FOSS-based Git server, please add in the comments section below.

Posted by: Vivek Gite
The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.

SHARE THIS ON (OR READ 0 COMMENTS/ADD ONE BELOW):
Twitter Facebook Google+
Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft takes a lot of my time and hard work to produce. If everyone who reads nixCraft, who likes it, helps fund it, my future would be more secure. You can donate as little as $1 to support nixCraft:

Become a Supporter Make a contribution via Paypal/Bitcoin

Tagged as: Tags DevOps, Linux, Programming, Sysadmin, Unix
Join the discussion at http://www.nixcraft.com
Post navigation
Previous post:
Previous post:Download Ubuntu Linux 18.04 LTS (Bionic Beaver) CD/DVD
nixCraft @2000-2018 nixCraft. All rights reserved.
PRIVACY
TERM OF SERVICE
CONTACT/EMAIL
DONATIONS
Hosted by Linode
DNS & CDN by Cloudflare
Designed and Developed by Prospect One Prospect One

Requiem for GitHub

Requiem for GitHub

Hintjens.com
MOVING PIECES

Search this site
Front
ZeroMQ
Community
Unprotocols
Psychopaths
Create account or Sign in
print

culture.jpg
Pieter Hintjens

Biography | Portfolio | GitHub
Books | Videos | Slides | Stories | Wiki
Contact

Thank you for the gifts

Kind people already sent donations that will make my childrens’ lives easier, when I’m not there any more. Thank you enormously! 🙂

Podcasts/Interviews

Software Engineering Daily
Interview by Adam Dymitruk
Ruby Rogues #188, Community Building

Talks on video

“De afspraak”, VRT, May 2016 (panel discussion)
RTL TV, May 2016 (panel discussion)
DomCode 2015, Nov 2015 (keynote)
Coding Serbia, Oct 2015 (keynote)
Euroscipy, Cambridge, Aug 2015 (keynote)
PyGrunn, Groningen, May 2015
Craft Conf, Budapest, Apr 2015
QCon London, Mar 2015
Code Mesh London, Nov 2014
Build Stuff, Vilnius 2014
Build Stuff, Vilnius, Nov 2014 (interview)
Coding Serbia, Nov 2014 (keynote)
Devnology, Leusden, Oct 2014 (keynote)
EuroPython, Berlin, Jul 2014 (keynote)
Build Stuff, Vilnius, Dec 2013 (interview)
Build Stuff, Vilnius, Dec 2013 (keynote)
Code Mesh London, Dec 2013
DevOps Days, London, Nov 2013
NDC Oslo, Jun 2013
CERN – Geneva, Jun 2013
Tech Mesh, London, Dec 2012
Strange Loop, St.Louis, Oct 2012
ZeroMQ@PDX part 2 – Portland, Feb 2012
ZeroMQ@PDX part 1 – Portland, Feb 2012
FLOSS Weekly, Petaliuma CA, Dec 2011
FOSDEM 2011, Brussels
Berlin Buzzwords 2010 (keynote)
FOSDEM 2009, Brussels
Hellenic FOSS Conference 2008, Athens part 1
Hellenic FOSS Conference 2008, Athens part 2

Email me at pieter@hintjens.com if you’d like me to present at your event, or in your organization.

BookCoverImage.jpg?random=0.4431380270844333
socarch.png
confessions.jpg
psccover.jpg
cat.gif
scalablec.png
BookCoverImage.jpg?random=0.7859812398986378
Download and read online

Legal | About | Contact

 ▽ 
Requiem for GitHub
All Articles » Requiem for GitHub

pieterhpieterh wrote on 7 Feb, 13:51 (848 days ago)

bullseye2.png
Since its birth in 2008, GitHub redefined how software developers worked together. The firm was famous for several reasons: it had no middle management, it had a strong remote working culture, it made exactly what we needed, no more or less, and it was always profitable. These are interrelated. Today, GitHub has 500 employees, is valued at $2bn, and I think it is dying. Here is why.

Fold
Table of Contents
A Prayer for the Dying
Update
Update 2 (2016/02/25)
A Prayer for the Dying
Let me start by saying I’ve been a fan of GitHub ever since I met a “GitHub guy” at a GitHub drink-up in Brussels. His job seemed to be to write code, and travel around the world drinking with other developers. I fell in love with company culture immediately.

Over the years, GitHub never disappointed me. Their platform was stable and reliable. Their UI remained minimalistic and as smooth fitting as a velvet glove. We switched our issue tracking from the venerable Jira to GitHub, watched with joy as it integrated with Travis and AppVeyor, and felt cosseted and safe with a firm that worked and thought as we did.

I’ve often turned to GitHub support for help. They have always been impeccable: fast, accurate service. It is unheard of for a web business. Obviously the staff loved their firm, loved their work, and loved their users. And we loved them right back.

Then in 2014 the drama started. It began with one recent employee making claims of harassment against the CEO and his wife. It turned nasty as anonymous rumors spread of this employee’s manipulative behavior in the firm, and her public tweets about being blackmailed towards sex with the CEO. The employee produced no evidence to back up her claims.

The founder and CEO resigned, his co-founder took over, and a new wind started to blow in the firm. A VP of Social Impact was hired. Middle management started to creep in. These new managers began to crack down on remote working. The old guard started to leave.

This story goes far beyond a fight between Social Justice Warriors and the Patriarchy for control over our Open Source dollars. This is not simply Death by Code of Conduct, though the firm did adopt a controversial CoC that disturbed not a few of its users.

I’ve written extensively on living systems and power pyramids, and the war between these two organizational models. GitHub was a rare example of a successful living system growing out of the fruit basket that is Silicon Valley. It quickly became dominant in its market. It was profitable, free of debt, growing, loved.

I’ve learned one thing from being an entrepreneur for 35 years: when there is unguarded money on the table, the wolves arrive. Call these as you like, their goal is to take the cash and blame someone else. They always use the same strategies, which I’ve documented extensively in my book The Psychopath Code. Divide and conquer. Charm and distraction. Promises and lies. Stealth, and violence.

GitHub is a treasure. Its current valuation of $2,000,000,000 is just a baseline. It is worth ten times more, at least, if handled right. What it needs to justify a $20bn price tag are three things:

The right structure. That means a power pyramid instead of this strange “horizontal” network.
The right size. 300 employees is too few. Yahoo! has 12,500 employees, and is worth $33bn on the stock market. GitHub needs at least 5,000 employees.
The right product. The minimalist GitHub we open source developers know and love does not appeal to corporate clients.
The first two changes are already happening. I predict the third will start to appear over the next six to 12 months. We’ll see acquisitions of firms like Travis, expansion of core features like the issue tracker, more sophisticated organization management, and so on. If you want to see what GitHub looks like in two years’ time, look at its traditional competitors.

GitHub will become more profitable. If I had shares in the firm I certainly would not sell them yet. Wait a year or two, until the VCs and new managers find their acquisition target. Wait until the exit, and then sell. After that, it’s over for the firm, and those who have stayed long enough to make their winnings will be looking for a way out.

There are people who will question my framing and the time-line. Let me explain this as a murder mystery. We see a healthy person suddenly falling sick. We see a massive insurance policy being taken out. This person’s demise is going to be extremely profitable. As investigator, you assume malice. You assume there exist people capable and willing to do anything if the price is right. You look for motive, means, opportunity. You follow the money.

Look at the timing of VC investment, hiring of new employees, internal political fights, ousting of old CEO, and hiring of new managers. Look at a climate in which political outsiders use the weapons of gender and race against meritocracy. What emerges is the picture of a slow fight between the owners and investors, and the founder/CEO. The owners want their billions. The founder/CEO wants to keep GitHub alive.

Unfortunately our data is incomplete. People are unwilling to speak publicly about what happened. We rely on claims and anonymous counter claims. Only time will tell.

I wish I was wrong. Yet we are seeing large-scale interference in a successful, and essential platform. This is not idle tinkering to solve diversity issues. This is not idealism. We are watching a fight over money and power. No, that’s wrong: there is no more fight. The founders of GitHub have already given in, one way or another. The fight is over. The users of GitHub won’t fight. They will either accept, or move away.

If my speculation is right, what will the impact be? GitHub have established themselves as The Place to host our public projects. Surely losing this will be a major blow to free and open source. Well, happily, no. This is how it happens: we construct, and then when we reach a plateau, the wolves come along, destroy what we make, and liberate us to go forth and do better. Psychopaths are the garbage collectors of human society.

Whither the ZeroMQ community? I’m going to begin using GitLab for my own projects, and if that works, I’ll aim to move the ZeroMQ projects that I care for, to GitLab, after discussion and consensus.

Thanks for all the fish, GitHub. And to all GitHubbers, thank you a million times. You did the impossible, and you did it right, and I wish you all the best for your next projects. Hugs. 🙂

Update
To my surprise, Julie Ann Horvath (JAH) commented on my tweet:

Julie Ann Horvath: @hintjens CEOs are very rarely (see: never) removed from a company without a claim being backed by substantial evidence.

So her argument is this: TPW agreed to step down, meaning he was “removed.” The implication is that TPW admitted guilt by quitting, despite GitHub’s investigation showing JAH’s two claims of harassment were false.

JAH backs up her argument by stating as a fact that CEOs are “never” removed from a company without substantial evidence. Thus, she implies, there was in fact substantial evidence against TPW. Note that she doesn’t actually say this, not has she ever produced such evidence.

From my own experience, CEOs and other managers tend to fight with shareholders and boards, and it’s this politics that forces them out. The role CEO is always temporary, annually renewable. Fired, or step down, it’s much the same. Anyhow, let’s look for some research on why CEOs get fired. Bingo: Forbes reports on a study of 286 organizations. Nothing about harassment. Rather:

My team and I interviewed 1,087 board members from 286 organizations that fired, or otherwise forced out, their chief executive. And we found that most CEOs get fired for “soft issues.” Thirty-one percent of CEOs got fired for poor change management, 28% for ignoring customers, 27% for tolerating low performers, 23% for denying reality and 22% for too much talk and not enough action.

I tweeted this to JAH.

Pieter Hintjens: @nrrrdcore that is false: study of 286 organizations showed majority fired for “soft” reasons: http://www.forbes.com/sites/markmurphy/2015/07/16/leadership-styles-are-often-why-ceos-get-fired/#21a2d28a6155

JAH dismissed this study using the “true Scotsman” argument:

Julie Ann Horvath: @hintjens in public companies, perhaps. Rerun those numbers against privately held/vc backed startups.

I pressed F5 to “rerun” the numbers and got exactly the same results. I’m not sure what else JAH would expect, as I did link her to the Forbes site. Nowhere does the article say it looked only at public companies. Nor is there any logical reason it would be different in a private firm from a public firm. I’d expect the standards of evidence to be higher in a public firm, if anything. I suspect she didn’t RTFA.

Here is a telling quote from Leadership IQ’s the original article:

“A more accurate explanation for why they get fired,” he added, “is that the Board of Directors or shareholders have lost confidence in their ability to generate sufficient financial returns in the future.”

In other words, it’s about money. And my hypothesis that TPW was pushed out in order to turn GitHub into a cash cow still stands.

Undeterred, JAH now attacks my motivations, instead of providing any real information:

Julie Ann Horvath: again, you’re reaching to make an argument that suits your interests and currently held position.

What position? I’m a writer. My position is, I write what I see. My interests are truth and knowledge. There’s no profit in this. Maybe JAH is projecting her own viewpoint.

Then JAH continues in a flood of tweets, presumably playing to her audience:

Julie Ann Horvath: @hintjens also, I don’t identify as an SJW, I’m merely a tech worker who happens to be a woman.

Did I call her a SJW? No, I said the situation at GitHub “goes far beyond a fight between Social Justice Warriors and the Patriarchy.” If anything I was saying, to the Reddit crowds, don’t read this as a classic SJW drama. It’s not.

JAH continues, to applause from her admirers, I assume:

Julie Ann Horvath: @hintjens I’m vocal, yes. But please don’t label me in a way that’s been known to incite violence against women.

Ah, here is the payload. She implies (no accusation, it’s the implication) that I’ve placed her in physical danger by labeling her. “Violence against women,” seems oddly specific coming from someone who doesn’t “identify as an SJW.” Why not simply “violence?” Is there a specific form of violence that… ah, yes, men the aggressors, women the victims. “Don’t call me SJW, that’s so cishet!”

Julie Ann Horvath: @hintjens all I’m asking for is the same respect you would award your own peers or employees.

Well, for a start I have no employees, and any peer who made unfounded accusations of sexual harassment would get the same treatment from me. This has happened to friends of mine, male and female, and my first response is: “do you have evidence, and if not, how can I believe your word over theirs?”

If someone abuses, harasses, or threatens you, get evidence any way you can (pull out your smartphone!) and then proceed.

There is a classic pattern here. Woman claims sexual harassment from powerful man. Man denies it. Woman goes to social media and makes her case in court of public opinion. Man cannot disprove a negative. Woman gets crowd of sympathetic admirers. Some may hate men. Some may feel guilt at their own misdeeds. Some may hate what that powerful man stands for. Some may just like being part of a mob.

The court of public opinion is not a court, and street justice is not justice. It is above all, the vulnerable and weak who get abused in such systems. We worked hard and long to build justice into the State.

If you are harassed or abused, you do not shout about it. You take it to the police and if they don’t act, you still have the choice of a private lawsuit. This is what courageous women and men do, when they are abused. What JAH did was something different. If you go to the public with a private matter, you lose your right to privacy.

JAH insists on dodging the reason we’re exchanging tweets: her public allegations, and her failure to produce any material evidence.

Julie Ann Horvath @hintjens I hope this conversation helps you better consider the implications of statements like these in the future.

Well, my statements stand, and now I have more to back them up. Talking to a professional victim always leaves one with a nasty, unpleasant feel. There’s no honesty there, just a shadow person who is good with charm and words. See, I didn’t made any accusations. Just the implication. Neat, isn’t it?

Julie Ann Horvath @hintjens and I also hope you have an awesome week 😊

Sweet. I’d have expected a little more backwash from 24.6K followers, yet there we are.

I then stumbled on Daniel Tennner’s article on GitHub. This is worth reading. A few choice quotes:

These are all very clear signs of an open culture that’s being ripped to shreds. It’s tragic. It’s also terribly ironic to see the old bullshit excuse that “well, it just doesn’t work at this scale” being deployed.

And:

When did the dream start dying? Who knows. But I have a hunch that it started before Tom Preston-Werner was ousted from the company, a couple of years ago, in a case of sexual harassment that was later dismissed as groundless. This smacks me of political play, a scenario very similar to what played out at AES, where an unrelated incident is used .. to weaken, attack or destroy it. GitHub was an open culture at the time. What did Tom do there? Why was it important to get him out?

This story isn’t over.

Update 2 (2016/02/25)
GitHub hires another political commissar, this one famous for enforcing political correctness on open source projects. The combination of charm and deniable brutality is familiar.

1 2 3 4 5

Comments
Add a New Comment
Hide All Comments Unfold All Fold All
Fold
It is happening
upescatoreupescatore 4 Jun 2018, 09:56
Hi Pieter, I wish you were here to see that today it is happening. Wolves arrive to buy GitHub.

As usual, with your intelligence you spotted that too!

Reply Options
Fold
Social Justice Warrior is a wrong word.
crocketcrocket 8 Mar 2016, 06:31
Social Justice Warrior is a badge of honor for extremist feminists.
The word confuses people. It is more appropriate to call them social control warriors. They are gestapo.

By calling them social control warriors, people will perceive truth more accurately.

Last edited on 8 Mar 2016, 06:32 by crocket Show more
Reply Options
Fold
yakshaving_jgtyakshaving_jgt 11 Feb 2016, 23:26
The court of public opinion is not a court, and street justice is not justice.

Beautiful.

Hear, hear, Hintjens.

Reply Options
Fold
Psychopaths may not be the garbage collectors.
crocketcrocket 8 Feb 2016, 23:57
Psychopaths are the garbage collectors of human society.

I started thinking that corporate psychopaths aren’t the garbage collectors but parasitic necromancers that murder corporations and turn them into the undead to work little and earn a lot as executives. When a corporation is taken over by psychopaths, it becomes inefficient. In a free market, inefficient corporations should be garbage-collected quickly. I saw psychopaths circumvent garbage collection of free market by connecting to government programs and infecting virtually every company.

In a hindsight, smart parasites should know that their hosts shouldn’t die quick. They can animate the undead corporations for decades. And, it takes a century or more for massive undead corporations like microsoft and google to die.

Silicon valley companies are an exception. Silicon valley VC investors learned to make money by liquidating companies as quick as possible.

Last edited on 11 Feb 2016, 01:50 by crocket Show more
Reply Options
Fold
Re: Psychopaths may not be the garbage collectors.
pieterhpieterh 10 Feb 2016, 15:53
Here is an interesting set of answers to this question: http://danieltenner.com/open-cultures/

Portfolio

Reply Options
Fold
GitHub didn’t have to be sold if its equity structure was different.
crocketcrocket 8 Feb 2016, 11:44
when there is unguarded money on the table, the wolves arrive.

Under a different equity model, GitHub could have been heavily guarded. GitHub could have been given a chance to fizzle out gracefully over decades or even centuries without being eaten by predators.

The design of the organization could be set up such that no one has power to sell the company without agreements from all groups of the organization. I believe it’s not difficult to engineer, in advance, a stalemate scenario for selling an organization.

For example, if 50% or more of a company’s equity is owned by another (possibly, non-profit and/or cooperative) entity that actually hires workers, selling the company requires the agreement of the other entity. The agreement cannot be derived by breaking the rules of the latter organization. I can engineer the latter organization so that nobody can sell the company without agreements from all groups in the organization.

It’s likely that GitHub’s equity structure allowed a few people to sell the company to any predator. Trusting a few people not to sell the company is like leaving money on the table.

we construct, and then when we reach a plateau, the wolves come along, destroy what we make, and liberate us to go forth and do better.

I think there is disgrace in how GitHub was eaten alive by predators. It was not a difficult prey for venture capitals.

Well guarded organizations have very good chances of defending themselves against wolves. I surmise even superhuman evil AIs cannot take over well-designed organizations directly with ease. Superhuman AIs will rather hack computers or launch DDoS attacks than buy or corrupt organizations.

I’d like to design new corporate systems that fizzle out gracefully and give employees enough time to move without allowing predators to eat companies alive. I argue that there can be grace in garbage collection.

Last edited on 11 Feb 2016, 01:50 by crocket Show more
Reply Options
Help | Terms of Service | Privacy | Report a bug | Flag as objectionable Powered by Wikidot.com
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License