It is also available as a DLC weapon for Silent Hill: Book of Memories.
Silent Hill 2
Pyramid Head is first seen using the Great Spear in 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 commit suicide together, impaling their heads on 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.
A theory based less on the symbolic significance and more of narrative convenience is that the switch from the Great Knife to the Great Spear occurs during the chase at the end of the hospital. Pyramid Head has been shown to be burdened greatly by wielding the Great Knife, only able to slowly drag it behind him, and as the scene plays out, he requires a much lighter weapon to properly give chase to James and Maria. Simply put, the scene would have no intensity if Pyramid Head had to slowly haul around the knife in his pursuit, so for the sake of dramatic tension, he was given the spear.