Удалить пользователя oracle вместе с табличным пространством
В этом учебном пособии вы узнаете, как использовать Oracle оператор ALTER TABLESPACE с синтаксисом и примерами.
Описание
Оператор ALTER TABLESPACE используется для изменения табличного пространства или одного из его файлов данных или временных файлов. Табличное пространство используется для выделения пространства в базе данных Oracle, где хранятся объекты схемы.
Синтаксис
Синтаксис для оператора ALTER TABLESPACE в Oracle / PLSQL:
ALTER TABLESPACE tablespace_name
< DEFAULT
[ < COMPRESS | NOCOMPRESS >] storage_clause
| MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| RESIZE integer [ K | M | G | T | P | E ]
| COALESCE
| RENAME TO new_tablespace_name
| < BEGIN | END >BACKUP
| < ADD < DATAFILE | TEMPFILE >
[ file_specification
[, file_specification ]
]
| DROP < 'filename' | file_number >
| RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
| < DATAFILE | TEMPFILE >< ONLINE | OFFLINE >
>
| < logging_clause | [ NO ] FORCE LOGGING >
| TABLESPACE GROUP < tablespace_group_name | '' >
| < ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
>
| READ < ONLY | WRITE >
| < PERMANENT | TEMPORARY >
| AUTOEXTEND
< OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE < UNLIMITED | integer [ K | M | G | T | P | E ] >]
>
| FLASHBACK < ON | OFF >
| RETENTION < GUARANTEE | NOGUARANTEE >
> ;
Параметры или аргументы
tablespace_name - имя табличного пространства для удаления из базы данных Oracle.
storage_clause - синтаксис для файла storage_clause:
STORAGE
( < INITIAL integer [ K | M | G | T | P | E ]
| NEXT integer [ K | M | G | T | P | E ]
| MINEXTENTS integer
| MAXEXTENTS < integer | UNLIMITED >
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL < KEEP | RECYCLE | DEFAULT >
>
[ INITIAL integer [ K | M | G | T | P | E ]
| NEXT integer [ K | M | G | T | P | E ]
| MINEXTENTS integer
| MAXEXTENTS < integer | UNLIMITED >
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL < KEEP | RECYCLE | DEFAULT >
]
)
file_specification - синтаксис для параметра file_specification:
< [ 'filename' | 'ASM_filename' ]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
[ AUTOEXTEND
< OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE < UNLIMITED | integer [ K | M | G | T | P | E ] >]
>
]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
>
Пример. Переименование файла данных.
Рассмотрим оператор ALTER TABLESPACE, который переименовывает файл данных, связанный с табличным пространством.
Use the DROP USER statement to remove a database user and optionally remove the user's objects.
In an Oracle Automatic Storage Management (Oracle ASM) cluster, a user authenticated AS SYSASM can use this clause to remove a user from the password file that is local to the Oracle ASM instance of the current node.
When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.
Do not attempt to drop the users SYS or SYSTEM . Doing so will corrupt your database.
CREATE USER and ALTER USER for information on creating and modifying a user
You must have the DROP USER system privilege. In an Oracle ASM cluster, you must be authenticated AS SYSASM .
Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.
Restriction on Dropping Users
You cannot drop a user whose schema contains a table that uses a flashback data archive for historical tracking. You must first disable the table's use of the flashback data archive.
Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.
If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
Oracle Database invalidates, but does not drop, the following objects in other schemas:
Views or synonyms for objects in the dropped user's schema
Stored procedures, functions, or packages that query objects in the dropped user's schema
Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
Oracle Database drops all triggers in the user's schema.
Oracle Database does not drop roles created by the user.
Oracle Database also drops with FORCE all types owned by the user. See the FORCE keyword of DROP TYPE.
Dropping a Database User: Example
If user Sidney's schema contains no objects, then you can drop sidney by issuing the statement:
If Sidney's schema contains objects, then you must use the CASCADE clause to drop sidney and the objects:
Many tasks, with many interwoven considerations, are involved in administering user privileges, roles, and profiles. These necessary operations and principles are discussed in the following sections:
Managing Oracle Users
Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. This section explains how to manage users for a database, and contains the following topics:
Oracle Database SQL Reference for more information about SQL statements used for managing users
Creating Users
You create a database user with the CREATE USER statement. To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.
Example 11-1 creates a user and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.
Example 11-1 Create a User and Grant the Create Session System Privilege
A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
As administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the CONNECT privilege did not need the additional privileges CONNECT used to provide. Instead, only CREATE SESSION was actually needed, and in fact that is the only privilege CONNECT presently retains.
Creating its own roles gives an organization detailed control of the privileges it assigns, and protects it in case Oracle changes the roles that it defines. For example, both CONNECT and RESOURCE roles will be deprecated in future Oracle versions.
This section refers to the preceding example as it discusses the following aspects of creating a user:
Granting System Privileges and Roles on page 25-11
Specifying a Name
Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.
Setting Up User Authentication
In Example 11-1, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.
Selecting and specifying the method of user authentication is discussed in "User Authentication Methods".
Assigning a Default Tablespace
Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle Database stores the object in the default user tablespace.
The default setting for the default tablespaces of all users is the SYSTEM tablespace. If a user does not create objects, and has no privileges to do so, then this default setting is fine. However, if a user is likely to create any type of object, then you should specifically assign the user a default tablespace. Using a tablespace other than SYSTEM reduces contention between data dictionary objects and user objects for the same data files. In general, it is not advisable for user data to be stored in the SYSTEM tablespace.
You can create a permanent default tablespace other than SYSTEM at the time of database creation, to be used as the database default for permanent objects. By separating the user data from the system data, you reduce the likelihood of problems with the SYSTEM tablespace, which can in some circumstances cause the entire database to become nonfunctional. This default permanent tablespace is not used by system users, that is, SYS , SYSTEM , and OUTLN , whose default permanent tablespace remains SYSTEM . A tablespace designated as the default permanent tablespace cannot be dropped. To accomplish this goal, another tablespace must first be designated as the default permanent tablespace. It is possible to ALTER the default permanent tablespace to another tablespace, affecting all users or objects created after the ALTER DDL commits.
You can also set a user default tablespace during user creation, and change it later with the ALTER USER statement. Changing the user default tablespace affects only objects created after the setting is changed.
When you specify the default tablespace for a user, also specify a quota on that tablespace.
In Example 11-1, the default tablespace for user jward is data_ts , and his quota on that tablespace is 500K .
Assigning Tablespace Quotas
You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does the following things:
Users with privileges to create certain types of objects can create those objects in the specified tablespace.
Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.
By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.
You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.
You can assign quotas to a user tablespace when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:
If a user has already exceeded a new tablespace quota, then the user's objects in the tablespace cannot be allocated more space until the combined space of these objects falls below the new quota.
If a user has not exceeded a new tablespace quota, or if the space used by the user's objects in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.
Revoking User Ability to Create Objects in a Tablespace
You can revoke the ability of a user to create objects in a tablespace by changing the current quota of the user to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.
UNLIMITED TABLESPACE System Privilege
To permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.
Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.
You can grant a user unlimited access to all tablespaces of a database with one statement.
The privilege overrides all explicit tablespace quotas for the user.
You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.
Assigning a Temporary Tablespace
Each user also should be assigned a temporary tablespace . When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace. These temporary segments are created by the system when doing sorts or joins and are owned by SYS , which has resource privileges in all tablespaces.
In the previous CREATE USER statement, the temporary tablespace of jward is temp_ts , a tablespace created explicitly to contain only temporary segments. Such a tablespace is created using the CREATE TEMPORARY TABLESPACE statement.
If the temporary tablespace of a user is not explicitly set, then the user is assigned the default temporary tablespace that was specified at database creation, or by an ALTER DATABASE statement at a later time. If there is no default temporary tablespace explicitly assigned, then the default is the SYSTEM tablespace or another permanent default established by the system administrator. It is not advisable for user data to be stored in the SYSTEM tablespace. Also, assigning a tablespace to be used specifically as a temporary tablespace eliminates file contention among temporary segments and other types of segments.
If your SYSTEM tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using the SYSTEM tablespace because temporary objects cannot be placed in permanent locally managed tablespaces.
You can set the temporary tablespace for a user at user creation, and change it later using the ALTER USER statement. Do not set a quota for temporary tablespaces. You can also establish tablespace groups instead of assigning individual temporary tablespaces.
Multiple Temporary Tablespaces: Using Tablespace Groups
Specifying a Profile
You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, then the user is assigned a default profile.
Setting Default Roles
You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default role setting for the user is ALL , which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER statement to change the default roles for the user.
Altering Users
Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.
You can alter user security settings with the ALTER USER statement. Changing user security settings affects the future user sessions, not current sessions.
The following statement alters the security settings for the user, avyrros :
The ALTER USER statement here changes the security settings for the user avyrros as follows:
Authentication is changed to use the operating system account of the user avyrros .
The default and temporary tablespaces are explicitly set for user avyrros .
avyrros is given a 100M quota for the data_ts tablespace.
The quota on the test_ts is revoked for the user avyrros .
avyrros is assigned the clerk profile.
Changing User Authentication Mechanism
Most non-DBA users can still change their own passwords with the ALTER USER statement, as follows:
No special privileges (other than those to connect to the database) are required for a user to change passwords. Users should be encouraged to change their passwords frequently.
Users must have the ALTER USER privilege to switch between methods of authentication. Usually, only an administrator has this privilege.
"User Authentication Methods" for information about authentication methods that are available for Oracle Database users
Changing User Default Roles
A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.
Dropping Users
When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped .
If a user schema and associated objects must remain but the user must be denied access to the database, then revoke the CREATE SESSION privilege from the user.
Do not attempt to drop the SYS or SYSTEM user. Doing so will corrupt your database.
A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause.
You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege.
If the user's schema contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.
The following statement drops the user, jones and all associated objects and foreign keys that depend on the tables owned by jones .
Oracle Database Administrator's Guide for more information about terminating sessions
Viewing Information About Database Users and Profiles
The wide variety of options that are available for viewing such information are discussed in the following subsections:
Use the ALTER USER statement:
To change the authentication or database resource characteristics of a database user
To permit a proxy server to connect as a client without authentication
In an Oracle Automatic Storage Management (Oracle ASM) cluster, to change the password of a user in the password file that is local to the Oracle ASM instance of the current node
Oracle Database Security Guide for detailed information about user authentication methods
In general, you must have the ALTER USER system privilege. However, the current user can change his or her own password without this privilege.
To change the password of a SYS user, the password file must exist.
You must be authenticated AS SYSASM to change the password of a user other than yourself in an Oracle ASM instance password file.
To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). If the current container is the root, then you can specify CONTAINER = ALL or CONTAINER = CURRENT . If the current container is a pluggable database (PDB), then you can specify only CONTAINER = CURRENT .
To set and modify CONTAINER_DATA attributes using the container_data_clause , you must be connected to a CDB and the current container must be the root.
The ke ywo rds , parameters, and clauses described in this section are unique to ALTER USER or have different semantics than they have in CREATE USER . Keywords, parameters, and clauses that do not appear here have the same meaning as in the CREATE USER statement.
Oracle recommends that user names and passwords be encoded in ASCII or EBCDIC characters only, depending on your platform.
CREATE USER for information on the keywords and parameters and CREATE PROFILE for information on assigning limits on database resources to a user
Specify BY password to specify a new password for the user. Passwords are case sensitive. Any subsequent CONNECT string used to connect this user to the database must specify the password using the same case (upper, lower, or mixed) that is used in this ALTER USER statement. Passwords can contain single-byte, or multibyte characters, or both from your database character set.
Oracle Database expects a different timestamp for each resetting of a particular password. If you reset one password multiple times within one second (for example, by cycling through a set of passwords using a script), then the database may return an error message that the password cannot be reused. For this reason, Oracle recommends that you avoid using scripts to reset passwords.
You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user's password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.
In an Oracle ASM cluster, you can use this clause to change the password of a user in the password file that is local to an Oracle ASM instance of the current node. You must be authenticated AS SYSASM to specify IDENTIFIED BY password without the REPLACE old_password clause. If you are not authenticated AS SYSASM , then you can only change your own password by specifying REPLACE old_password .
Oracle Database does not check the old password, even if you provide it in the REPLACE clause, unless you are changing your own existing password.
Oracle Database Security Guide for guidelines on creating passwords
Refer to CREATE USER for more information on this clause.
You can change a user's access verification method from IDENTIFIED GLOBALLY to either IDENTIFIED BY password or IDENTIFIED EXTERNALLY . You can change a user's access verification method to IDENTIFIED GLOBALLY from one of the other methods only if all external roles granted explicitly to the user are revoked.
Refer to CREATE USER for more information on this clause.
NO AUTHENTICATION Clause
Use this clause to change an existing user account with authentication to a schema account without authentication to prevent logins to the account.
DEFAULT COLLATION Clause
Use this clause to change the default collation for the schema owned by the user. The new default collation is assigned to tables, views, and materialized views that are subsequently created in the schema. It does not influence default collations for existing tables views, and materialized views. Refer to the DEFAULT COLLATION Clause clause of CREATE USER for the full semantics of this clause.
DEFAULT TABLESPACE Clause
Use this clause to assign or reassign a tablespace for the user's permanent segments. This clause overrides any default tablespace that has been specified for the database.
Restriction on Default Tablespaces
You cannot specify a locally managed temporary tablespace, including an undo tablespace, or a dictionary-managed temporary tablespace, as a user's default tablespace.
[LOCAL] TEMPORARY TABLESPACE Clause
Use this clause to assign or reassign a temporary tablespace or tablespace group for the user's temporary segments.
Specify tablespace to indicate the user's temporary tablespace. Specify TEMPORARY TABLESPACE to indicate a shared temporary tablespace. Specify LOCAL TEMPORARY TABLESPACE to indicate a local temporary tablespace. If you are connected to a CDB, then you can specify CDB$DEFAULT to use the CDB-wide default temporary tablespace.
Specify tablespace_group_name to indicate that the user can save temporary segments in any tablespace in the tablespace group specified by tablespace_group_name . Local temporary tablespaces cannot be part of a tablespace group.
Restriction on User Temporary Tablespace
Any individual tablespace you assign or reassign as the user's temporary tablespace must be a temporary tablespace and must have a standard block size.
DEFAULT ROLE Clause
Specify the roles enabled by default for the user at logon.This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to specify:
Roles not granted to the user
Roles granted through other roles
Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory
Roles that are enabled by the SET ROLE statement, such as password-authenticated roles and secure application roles
Assigning Default Roles to Common Users in a CDB
You can modify the default role assigned to a common user both in the current container and across all containers in a CDB.
While assigning a default role to a common user across all containers, role must be a common role that was commonly granted to the common user.
While assigning a default role to a common user in the current container, role must be one of the following:
A local role that was granted to the common user in the current container
A common role that was granted to the common user, either commonly or locally in the current container
This clause is not reversible. Specify ENABLE EDITIONS to allow the user to create multiple versions of editionable objects in this schema using editions. Editionable objects in non-editions-enabled schemas cannot be editioned.
Use the FOR clause to specify one or more object types for which the user can create editionable objects. For a list of valid values for object_type , query the V$EDITIONABLE_TYPES dynamic performance view. If you omit the FOR clause, then the user can create editionable objects for all editionable object types.
Oracle Database Reference for more information about the V$EDITIONABLE_TYPES dynamic performance view
If the schema to be editions-enabled contains any objects that are not editionable and that depend on editionable type objects in the schema, then you must specify FORCE to enable editions for this schema. In this case, all the objects that are not editionable and that depend on the editionable type objects in the schema being editions-enabled become invalid.
If the current container is a PDB, then you can specify CONTAINER = CURRENT to change the attributes of a local user, or the container-specific attributes (such as the default tablespace) of a common user, in the current container. If the current container is the root, then you can specify CONTAINER = ALL to change the attributes of a common user across the entire CDB. If you omit this clause and the current container is a PDB, then CONTAINER = CURRENT is the default. If you omit this clause and the current container is the root, then CONTAINER = ALL is the default.
Restriction on Modifying Common Users in a CDB
Certain attributes of a common user must be modified for all the containers in a CDB and not for only some containers. Therefore, when you use any of the following clauses to modify a common user, ensure that you modify all of the containers by connecting to the root and specifying CONTAINER=ALL :
The container_data_clause allows you the set and modify CONTAINER_DATA attributes for a common user. Use the FOR clause to indicate whether to set or modify the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute. These attributes determine the set of containers (which can never exclude the root) whose data will be visible via CONTAINER_DATA objects to the specified common user when the current session is the root.
To specify the container_data_clause , the current session must be the root and you must specify CONTAINER = CURRENT .
Use this clause to set the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. When you specify this clause, you replace the existing value, if any, of the CONTAINER_DATA attribute.
Use container_name to specify one or more containers that will be accessible to the user.
Use ALL to specify that all current and future containers in the CDB will be accessible to the user.
Use DEFAULT to specify the default behavior, which is as follows:
For a default CONTAINER_DATA attribute, the current container, that is, the root, and the CDB as a whole will be accessible to the user.
For an object-specific CONTAINER_DATA attribute, the database will use the user's default CONTAINER_DATA attribute.
CONTAINER_DATA attributes that are set to DEFAULT are not visible in the DBA_CONTAINER_DATA view.
Use this clause to add containers to the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. Use container_name to specify one or more containers to add.
You cannot use this clause if the default CONTAINER_DATA attribute is set to ALL . If you use this clause when the default CONTAINER_DATA attribute is set to DEFAULT , then CDB$ROOT will automatically be added to the set of containers, unless the set already contains CDB$ROOT .
You cannot use this clause if the object-specific CONTAINER_DATA attribute is set to ALL or DEFAULT .
Use this clause to remove containers from the default CONTAINER_DATA attribute or an object-specific CONTAINER_DATA attribute for a common user. Use container_name to specify one or more containers to remove.
You cannot use this clause if the default CONTAINER_DATA attribute or object-specific CONTAINER_DATA attribute is set to ALL or DEFAULT .
If you specify the FOR clause, then you can set and modify the object-specific CONTAINER_DATA attribute for container_data_object for a common user. container_data_object must be a CONTAINER_DATA table or view. If you omit schema , then Oracle Database assumes that container_data_object is in your own schema.
If you omit the FOR clause, then you can set and modify the default CONTAINER_DATA attribute for a common user.
Oracle Database Security Guide for more information about enabling common users to view information about PDB objects
The proxy_clause lets you control the ability of an enterprise user (a user outside the database) or a database proxy (another database user) to connect as the database user being altered.
GRANT CONNECT THROUGH
Specify GRANT CONNECT THROUGH to allow the connection.
REVOKE CONNECT THROUGH
Specify REVOKE CONNECT THROUGH to prohibit the connection.
This clause lets you expose user to proxy use by enterprise users. The administrator working in Oracle Internet Directory must then grant privileges for appropriate enterprise users to act on behalf of user .
This clause lets you expose user to proxy use by database user db_user_proxy (the proxy).
The proxy will have all privileges that were directly granted to user.
The proxy will have all roles associated with user, unless you specify the WITH clauses of db_user_proxy_clauses to limit the proxy to some or none of the roles of user. For each role associated with the proxy, if the role is enabled by default for user at login, then that role will also be enabled by default for the proxy at login.
Specify the WITH clauses to limit the proxy to some or none of the roles associated with user , and the AUTHENTICATION REQUIRED clause to specify whether authentication is required.
WITH ROLE role_name permits the proxy to connect as the specified user and to activate only the roles that are specified by role_name . This clause can contain only roles that are associated with user .
WITH ROLE ALL EXCEPT
WITH ROLE ALL EXCEPT role_name permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified for role_name . This clause can contain only roles that are associated with user .
WITH NO ROLES permits the proxy to connect as the specified user, but prohibits the proxy from activating any of that user's roles after connecting.
Oracle Database does not expect the proxy to authenticate the user unless you specify the AUTHENTICATION REQUIRED clause. This clause ensures that authentication credentials for the user must be presented when the user is authenticated through the specified proxy. The credential is a password.
The AUTHENTICATED USING clauses, which appeared in the syntax of earlier releases, have been deprecated and are no longer needed. If you specify the AUTHENTICATED USING PASSWORD clause, then Oracle Database converts it to the AUTHENTICATION REQUIRED clause. Specifying the AUTHENTICATED USING CERTIFICATE clause or the AUTHENTICATED USING DISTINGUISHED NAME clause is equivalent to omitting the AUTHENTICATION REQUIRED clause.
Oracle Security Overview for an overview of database security and for information on middle-tier systems and proxy authentication
Oracle Database Security Guide for more information on proxies and their use of the database and "Proxy Users: Examples"
Changing User Identification: Example
The following statement changes the password of the user sidney (created in "Creating a Database User: Example" ) second_2nd_pwd and default tablespace to the tablespace example :
The following statement assigns the new_profile profile (created in "Creating a Profile: Example" ) to the sample user sh :
In subsequent sessions, sh is restricted by limits in the new_profile profile.
The following statement makes all roles granted directly to sh default roles, except the dw_manager role:
At the beginning of sh 's next session, Oracle Database enables all roles granted directly to sh except the dw_manager role.
Changing User Authentication: Examples
The following statement changes the authentication mechanism of user app_user1 (created in "Creating a Database User: Example" ) :
If you cause a database user's password to expire with PASSWORD EXPIRE , then the user (or the DBA) must change the password before attempting to log in to the database following the expiration. However, tools such as SQL*Plus allow the user to change the password on the first attempted login following the expiration.
Assigning a Tablespace Group: Example
The following statement assigns tbs_grp_01 (created in "Adding a Temporary Tablespace to a Tablespace Group: Example" ) as the tablespace group for user sh :
Proxy Users: Examples
The following statement alters the user app_user1 . The example permits the app_user1 to connect through the proxy user sh . The example also allows app_user1 to enable its warehouse_user role (created in "Creating a Role: Example" ) when connected through the proxy sh :
To show basic syntax, this example uses the sample database Sales History user ( sh ) as the proxy. Normally a proxy user would be an application server or middle-tier entity. For information on creating the interface between an application user and a database by way of an application server, refer to Oracle Call Interface Programmer's Guide.
"Creating a Role: Example" to see how to create the dw_user role
The following statement takes away the right of user app_user1 to connect through the proxy user sh :
The following hypothetical examples shows another method of proxy authentication:
The following example exposes the user app_user1 to proxy use by enterprise users. The enterprise users cannot act on behalf of app_user1 until the Oracle Internet Directory administrator has granted them appropriate privileges:
Иногда нужно избавиться от табличного пространства. Удалить табличное пространство из базы Oracle Database можно, выполнив следующую команду:
Если табличное пространство test01 включает таблицы или индексы на момент ввода команды DROP TABLESPACE, то вы получите ошибку. Вы можете либо переместить все объекты в другое табличное пространство, либо, если эти объекты не нужны, просто использовать следующую команду, которая уничтожит табличное пространство вместе со всеми объектами, входящими в него:
Внимание! В Oracle Database 10g объекты базы, такие как таблицы, не уничтожаются окончательно при выдаче команды DROP TABLE. Вместо этого они отправляются в корзину (Recycle Bin), откуда их можно потом восстановить. При использовании команды DROP TABLESPACE. INCLUDING CONTENTS объекты табличного пространства уничтожаются окончательно, не попадая в корзину! Любые объекты, относящиеся к этому табличному пространству и находящиеся в корзине, также уничтожаются безвозвратно при выполнении этой команды. Если опустить конструкцию INCLUDING CONTENTS, а табличное пространство содержит объекты, оператор даст сбой, но при этом все объекты из корзины все-таки будут уничтожены.
Оператор DROP TABLESPACE. INCLUDING CONTENTS не освободит файлы данных для операционной системы. Чтобы сделать это, потребуется либо вручную удалить файлы данных, бывшие частью табличного пространства, либо выполнить следующую команду, чтобы удалить и объекты, и файлы данных сразу:
Приведенный оператор автоматически уничтожит файлы данных вместе с табличным пространством.
Если в других таблицах окажутся активные ограничения целостности, ссылающиеся на таблицы в табличном пространстве, которое вы собираетесь уничтожить, нужно будет использовать такую команду:
Единственное табличное пространство, которое нельзя уничтожить — это, конечно,ваше табличное пространство System. Кроме того, во время обычной операции базы данных невозможно уничтожить табличное пространство Sysaux. Однако имея привилегию SYSDBA и запустив базу данных в режиме MIGRATE, удалить табличное пространство Sysaux можно.
Разумеется, что причин для уничтожения табличного пространства Sysaux не так много. Если просто нужно перенести определенных пользователей из этого табличного пространства, вы всегда можете воспользоваться соответствующей процедурой перемещения, специфицированной в представлении V$SYSAUX_OCCUPANTS.
Представление V$SYSAUX_OCCUPANTS покажет подробности о пространстве, занятом каждым элементом табличного пространства System. Оно также предоставит процедуру перемещения для заданного элемента, если вы захотите переместить его в другое табличное пространство. Вот простой запрос, использующий это представление:
Если необходимо переместить элемент ULTRASEARCH из System в новое табличное пространство по имени ULTRA1, это можно сделать с помощью процедуры MOVE_WK, принадлежащей схеме WKYS, как показано ниже:
Далее в моих заметках будет представлено несколько полезных представлений словаря данных, которые помогут управлять базой данных Oracle Database. Хотя использование OEM Database Control сокращает необходимость в частом обращении к большинству из этих представлений, важно знать об их существовании и содержимом, чтобы иметь понятие, где база данных хранит важную информацию.
Читайте также: