\section{Introduction}
gives something like
1. Introduction
How can I rather obtain the following header?
\section{Introduction}
gives something like
How can I rather obtain the following header?
Without using hyperref and within the default document classes, you could patch \@sect to conditionally insert Section~ based on whether you are in \section or not:

\documentclass{article}
\usepackage{etoolbox}% http://ctan.org/pkg/etoolbox
\makeatletter
\def\@seccntformat#1{\csname the#1\endcsname.\quad}
\patchcmd{\@sect}% <cmd>
{\relax\@svsec}% <search>
{\relax\ifnum#2=1\relax Section~\fi\@svsec}% <replace>
{}{}% <success><failure>
\makeatother
\begin{document}
\section{A section}\label{section}
\subsection{A subsection}
See Section~\ref{section}.
\end{document}
The patch is provided by etoolbox's \patchcmd. It searches fo \relax\@svsec and inserts the condition inbetween. The condition checks if the level of the sectional unit matches "level 1" (that of \section), as is the case when looking at the definition in article.cls (the second argument to \@startsection):
\newcommand\section{\@startsection {section}{1}{\z@}%
{-3.5ex \@plus -1ex \@minus -.2ex}%
{2.3ex \@plus.2ex}%
{\normalfont\Large\bfseries}}
For detail on the components of \@startsection, see Where can I find help files or documentation for commands like \@startsection for LaTeX?
\@svsec is.
– Tyler Crompton
Oct 21 '15 at 20:32
You can use a very useful feature of TeX; a command called as
\csname foo\endcsname
will be silently ignored (actually \relax would be executed, which does nothing) if \foo isn't defined.
So doing
\documentclass{article}
\makeatletter
\def\@seccntformat#1{\csname named#1\endcsname\csname the#1\endcsname.\quad}
\makeatother
\newcommand{\namedsection}{Section }
\begin{document}
\section{A section}\label{section}
\subsection{A subsection}
See Section~\ref{section}.
\end{document}
you reach your aim.

If you also want to add "Subsection" in front of the subsection number, just add
\newcommand{\namedsubsection}{Subsection }
The macro \@seccntformat is described, with similar tricks, in the TeX FAQ, see Adjusting the presentation of section numbers
\def\thesection{Section~\arabic{section}.}– Eddy_Em Feb 04 '13 at 07:19\documentclassare you using? – Werner Feb 04 '13 at 07:25