Here is a bit of a dog's breakfast of provisos and tidbits to go with VM9's rather definitive answer.
Another Pathological Example
One can construct even more pathological examples than VM9's: consider the function
$$\psi(x) = \left\{\begin{array}{cl}1& x\in\mathbb{Q}\\0& x\in\mathbb{R}-\mathbb{Q}\end{array}\right.$$
However, this functions is generally thought of, for the purposes of $\mathbf{L}^2(\mathbb{R})$, as being the same as the function $\psi:\mathbb{R}\to\mathbb{C};\;\psi(x) = 0$: one wontedly considers, strictly speaking, equivalence classes of functions where we deem $\psi_1\sim\psi_2$ if the Lebesgue measure $\mu(\{x\in\mathbb{R}: \psi_1(x) \neq \psi_2(x)\})$ of the set $\{x\in\mathbb{R}: \psi_1(x) \neq \psi_2(x)\}$ is nought. We call functions which are not equal but equivalent by the relation $\sim$ "equal almost everywhere". We must interpret the Hilbert space's completeness this way: the Hermite function expansion of my pathological $\psi$ is the same as the expansion for $\psi:\mathbb{R}\to\mathbb{C};\;\psi(x) = 0$. Moreover, this equivalence makes perfect sense physically: there is zero probability of observing a particle in a subset $U\subset\mathbb{R}$ if a wavefunction $\psi$ is nought almost everywhere in that subset, i.e. equal to nought there aside from within a set $V\subset U$ of measure nought ($\mu(V)=0$).
Nonnormalisable States
Often we also want to think of nonnormalisable states: for example the state $\psi(x) = e^{i\,k\,x}$ in position co-ordinates, the "momentum eigenstate" with precisely known momentum $\hbar\,k$ but totally delocalised in position co-ordinates, or for a second important example $\psi(x) = \delta(x)$: the Dirac delta, in the distributional sense. We can then build normalisable states up from continuous superpositions of these states: the Fourier transform, by definition, resolves any $psi\in\mathbf{L}^2(\mathbb{R})$ into a superposition of delocalised momentum eigenstates $e^{i\,k\,x}$. To reason with and wield these nonnormalised states properly, we must call on the idea of rigged Hilbert space and think about the class of tempered distributions rather than $\mathbf{L}^2(\mathbb{R})$; see my answer here and also this one here for some more details.
Conditions for Normalisability
I bring to your attention the conditions whereunder we get normalisable eigenstates. See QMechanic's pithy summary here; normalisable eigenstates needfully correspond to the discrete spectrum of an operator and this often boils down to whether an operator can in some way be construed as acting on a compact phase space.
Another neat family of normalisable eigenstates comes from, of all places, the field of optical waveguide theory, where one looks for bound eigenmodes (normalisable states) of the Helmholtz equation $(\nabla^2 + k^2 n(x,y,z)^2)\psi = \beta^2 \psi$. Here $n(x,y,z)$ is the refractive index profile of a waveguide. We have the following result - I can't put my hand on the proof right now but I shall look for it.
Theorem Suppose the refractive index profile $n:\mathbb{R}^3\to \mathbb{R}$ is such that $n(x,y,z)\to n_0$ as $\sqrt{x^2+y^2+z^2}\to\infty$. Then there there are bound modes $\psi\in\mathbf{L}^2(\mathbb{R}^3)$ of the Equation $(\nabla^2 + k^2 n(x,y,z)^2)\psi = \beta^2 \psi$ if and only if:
$$\int_{\mathbb{R}^3} (n(x,y,z)^2 - n_0^2) \,{\rm d}x\,{\rm d} y\, {\rm d} z> 0$$
and the discrete eigenvalues $\beta$ lie in the interval $[n_0,\,\max(k \,n(x,y,z))]\qquad\square$.
Analogous results for $\mathbf{L}^2(\mathbb{R}^2)$ (i.e. for a translationally invariant waveguide, where we apply the theorem to the 2D transverse progile $n(x,y)$).
This theorem can clearly immediately be applied to the finding of discrete eigenfunctions of the Schrödinger equation: we simply replace $k^2 n(x,y)^2$ by $E_{max} - V(x,y,z)$, where $E_{max}$ is an overbound for energy of the energy eigenvalues we seek. The discrete eigenvalues will lie in between $V(\infty)$ and $E_{max}$ if and only if $E_{max}$ is big enough that $\int_{\mathbb{R}^3} (E_{max} - V(x,y,z)) \,{\rm d}x\,{\rm d} y\, {\rm d} z> 0$. So the theorem can give us sufficient conditions for normalisable eigenstates and find lower bounds for the ground state energy.
Similar results show that if $V(x,y,z)$ is finite for all $\mathbb{R}^3$ but $V\to+\infty$ as $\sqrt{x^2+y^2+z^2}\to\infty$, then there are countably many normalisable eigenstates and they span the whole Hilbert space $\mathbf{L}^2(\mathbb{R}^3)$. The quantum harmonic oscillator falls into this category (as does the upside-down parabolic refractive index profile optical fibre in optical waveguide theory).
Behaviour in Classically "Forbidden" Regions
You ask about quantum possibilities like:
...it's possible in theory for a human being to walk right through a concrete wall (though the probability of this happening is of course so close to zero so as to be negligible). I don't necessarily question that such things are possible, but I want to know what the limitations are.
Here it might be enlightening to consider the behaviour of the wavefunction in classically forbidden regions. A good system to solve is to find the normalisable energy eigenstates for the finite square well potential. Look at the Wiki page and take particular heed that in the classically forbidden region, the wavefunction is always evanescent in classically forbidden regions: that is, it is nonzero there, but it decays exponentially with increasing depth into the classically forbidden region. What this means practically is that the probability to find the particle any distance into the classically forbidden region swiftly becomes fantastically small after a few wavelengths' penetration. Practically, the classically forbidden region is also pretty much quantum forbidden too, unless we are talking really small penetrations. This is the reason for the wide range of radioactive half lives amongst the elements: a truly tiny potential barrier to decay means decay can happen swiftly, but even a modest increase in the potential barrier causes a many orders of magnitude slump in the decay rate.
