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.