Create and Manage Database User Accounts in Software

Painting QR Code in Software Create and Manage Database User Accounts

If a default tablespace is not specified when creating the database, it will be set to the SYSTEM tablespace After creating a database, do not leave the default tablespace as SYSTEM; this is very bad practice Either change it as soon as you have created another tablespace, or after the CREATE DATABASE command has in fact let you create a default tablespace A quota is the amount of space in a tablespace that a user is allowed to occupy He can create objects and allocate extents to them until the quota is reached If he has no quota on a tablespace, he cannot create any objects at all Quotas can be changed at any time If a user s quota is reduced below the size of the objects he already owns (or even reduced to zero), the objects will survive and will still be usable, but they will not be permitted to get any bigger Figure 8-2 shows how to investigate and set quotas The first query in the figure is against DBA_USERS and determines the default and temporary tablespaces for the user JOHN, created in Figure 8-1 DBA_USERS has one row for every user account in the database User JOHN has picked up the database defaults for the default and temporary tablespaces, which are shown in the last query against DATABASE_PROPERTIES

FIGURE 8-2

8:

The two ALTER USER commands in Figure 8-2 give JOHN the capability to take up to 10 MB of space in the USERS tablespace, and an unlimited amount of space Before you can create a in the EXAMPLE tablespace The query table, you must have both permission to against DBA_TS_QUOTAS confirms this; the execute CREATE TABLE and a quota on a figure 1 is how unlimited is represented tablespace in which to create it At the time the query was run JOHN had not created any objects, so the figures for BYTES are zeros, indicating that he is not currently using any space in either tablespace Most users will not need any quotas, because they will never create objects They will only have permissions against objects owned by other schemasThe few object-owning schemas will probably have QUOTA UNLIMITED on the tablespaces where their objects reside

Permanent objects (such as tables) are stored in permanent tablespaces; temporary objects are stored in temporary tablespaces A session will need space in a temporary tablespace if it needs space for certain operations that exceed the space available in the session s PGA Remember that the PGA is the program global area, the private memory allocated to the session Operations that need temporary space (in memory if possible, in a temporary tablespace if necessary) include sorting rows, joining tables, building indexes, and using temporary tables Every user account is assigned a temporary tablespace, and all user sessions connecting to the account will share this temporary tablespace The query against DBA_USERS in Figure 8-2 shows user JOHN s temporary tablespace, which is the database default temporary tablespace This is shown by the last query in Figure 8-2, against DATABASE_PROPERTIES Space management within a temporary tablespace is completely automatic Temporary objects are created and dropped as necessary by the database A user does not need to be granted a quota on his/ her temporary tablespace This is because the objects in it are not actually owned by him/her; they are owned by the SYS user, who has an Users do not need a unlimited quota on all tablespaces quota on their temporary tablespaces

To change a user s temporary tablespace (which will affect all future sessions that connect to the account), use an ALTER USER command:

ALTER USER username TEMPORARY TABLESPACE tablespace_name;

If many users are logging on to the same user account, they will share use of one temporary tablespaceThis can be a performance bottleneck, which may be avoided by using temporary tablespace groups