In our organization, we have several databases. Each of them has lots of schemas with tons of packages, procedure, functions and so on. Now, I wonder what approach is best in terms of organizing those objects in a git repository. Should I have repositories based on schemas or application they're used by? There are lots of objects across different schemas that are used by multiple applications. So if I want to organize them by applications, then some objects will have to be in multiple repositories, which will obviously lead to having to update one object several times in different repositories. On the other hand, although keeping them by their schemas might sound a better idea, that way I won't have much clue about where these objects are used.
Organizing by schema is the approach I would take. We use a similar layout at work, and I feel it just makes sense to have the same structures/navigation in source control as see in my PL/SQL IDE.
This also allows us to easily package up an entire schema into one versioned sql script that we can deploy to our various environments, which works well for our processes, but YMMV.
We usually organized by schema or group of related schemas, or used a monorepo.
I'd go with a monorepo unless there are serious concerns that prevent that, e.g. access rights isolation, or a colossal scale, and a pull takes many minutes. When splitting, I'd try to come up with as few repos as possible. Since you're using git, stepping on other teams' toes is not a concern.
Things that need to change in concert should be in the same repo and be able to change in the same commit. I would definitely not split a schema between two repos.
Things that need to be deployed together are easiest to keep in the same repo.