It is also available as a DLC weapon for Silent Hill: Book of Memories.
Silent Hill 2
It is first used in the Brookhaven Hospital when he kills Maria. He also uses it in the labyrinth, then again in Lakeview Hotel with the second Pyramid Head, serving as the penultimate boss. After being defeated, the Pyramid Heads suicide together, impaling their heads with their spears.
Because of the "Death by Skewering" painting found in the Silent Hill Historical Society (which depicts Lying Figures being pierced by tree branches, which can also be seen as prisoners being pierced by sharp objects) and a picture of two Pyramid Heads holding spears found in the Toluca Prison, it can be theorized that spears were a possible form of execution in the prison, as "to choose his death was the prisoner's last taste of freedom".
The stone monument on Martin St. also states "...the executioners poured the water used to wash the execution tools in here". This suggests that the prison used more than just nooses to execute people. Since Pyramid Head takes the role of an executioner/punisher of sins, Pyramid Head wielding a spear can be seen as a reflection of the executioners of Toluca Prison who would use spears to kill prisoners, and since James Sunderland is a sinner for murdering his wife, Pyramid Head must fulfill his role by punishing him.
Another theory is that the release of the Great Knife is the release of a burden on James. The death of Maria signifies the death of Mary, and with Mary gone, he no longer has to take care of an ill spouse. This is a huge weight off of James's shoulders, which could be symbolized by Pyramid Head's switch to the spear: a much lighter and durable weapon.
Considering the sexual nature of the Silent Hill series, it's not out of the question that the Great Spear may have phallic symbolism.