Oracle какие права нужны для truncate table
Summary: in this tutorial, you will learn how to use the Oracle GRANT statement to give privileges to a specific user.
Truncate Table Cascade
- Before Oracle 12c, You cannot truncate the parent table of an enabled foreign key constraint. If you try it, you will get ORA-02266. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
- With Oracle 12c R1, Oracle has introduced Cascade clause for Truncate. We have to Specify CASCADE allowing you to recursively truncate down the tables in a hierarchy If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not truncate the table. Lets understand this truncate table with cascade with an example
It is important to note foreign key constraints should have an ON DELETE CASCADE for this to work. It is an important point to note truncate table with cascade not only deletes data from the DEPT table only but it also deletes the EMP table.
4) Using Oracle GRANT to grant object privileges to a user example
First, launch the first SQL*Plus session, log in as ot user and create a new table named t2 :
Second, insert some values into the t2 table:
Third, launch the second SQL*Plus session, log in as john , and query data from the ot.t2 table:
Oracle issued the following error:
This is because the user john does not have a privilege to query data from the ot.t2 table.
Fourth, go back to the first SQL*Plus session and grant the SELECT object privilege on ot.t2 to john :
Fifth, go the second session SQL*Plus and query data from the ot.t2 table:
Now, john should be able to query data from the ot.t2 table.
Sixth, try to insert some rows into the ot.t2 table:
Oracle issued the following error:
To allow john to insert and update data in the ot.t2 table, you need to grant the INSERT and UPDATE object privilege to john :
Now, john should be able to insert and update data in the ot.t2 table.
In this tutorial, you have learned how to use the Oracle GRANT statement to assign system and object privileges to a specific user.
You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process
Grant object privileges on the table
Create the indexes, integrity constraints, and triggers on the table
Specify the storage parameters of the table
Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
DELETE and DROP TABLE for information on other ways of removing data from a table
TRUNCATE CLUSTER for information on truncating a cluster
To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.
To specify the CASCADE clause, all affected child tables must be in your schema or you must have the DROP ANY TABLE system privilege.
You can truncate a private temporary table with the existing TRUNCATE TABLE command. Truncating a private temporary table will not commit and existing transaction. This applies to both transaction-specific and session-specific private temporary tables. Note that a truncated private temporary table will not go into the RECYCLEBIN .
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema , then Oracle Database assumes the table is in your own schema.
You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.
Oracle Database changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.
Oracle Database also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table : range and hash partitions of local indexes and subpartitions of local indexes.
If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment. This operation is equivalent to creating a new segment for the index. Therefore, at the end of the truncate operation, the indexes are once again USABLE .
For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.
If a regular or index-organized table contains LOB columns, then all LOB data and LOB index segments are truncated.
If table is partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log. If this direct-path INSERT information is removed, then an incremental refresh of the materialized view may lose data.
All cursors are invalidated.
Restrictions on Truncating Tables
This statement is subject to the following restrictions:
You cannot roll back a TRUNCATE TABLE statement.
You cannot flash back to the state of the table before the truncate operation.
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
If a domain index is defined on table , then neither the index nor any index partitions can be marked IN_PROGRESS .
You cannot truncate the parent table of a reference-partitioned table. You must first drop the reference-partitioned child table.
You cannot truncate a duplicated table.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export or import without affecting the ability of primary key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary key materialized views, the materialized view log must record primary key information.
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Specify PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.
Specify PURGE if any materialized view log should be purged when the master table is truncated.
Oracle Database Administrator’s Guide for more information about materialized view logs and the TRUNCATE statement
The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause, DROP ALL STORAGE clause, and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.
Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.
DROP ALL STORAGE
Specify DROP ALL STORAGE to deallocate all space from the deleted rows from the table, including the space allocated by the MINEXTENTS parameter. All segments for the table, as well as all segments for its dependent objects, will be deallocated.
Restrictions on DROP ALL STORAGE
This clause is subject to the same restrictions as described in "Restrictions on Deferred Segment Creation" .
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.
This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future.
This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.
If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
If you specify CASCADE , then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.
Truncating a Table: Example
The following statement removes all rows from a hypothetical copy of the sample table hr.employees and returns the freed space to the tablespace containing employees :
The preceding statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.
Preserving Materialized View Logs After Truncate: Example
The following statements are examples of TRUNCATE statements that preserve materialized view logs:
Use the GRANT statement to grant:
Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined. For a list of predefined roles, refer to Oracle Database Security Guide .
Global roles (created with IDENTIFIED GLOBALLY ) are granted through enterprise roles and cannot be granted using the GRANT statement.
Notes on Authorizing Database Users
You can authorize database users through means other than the database and the GRANT statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Some operating systems have facilities that let you grant roles to Oracle Database users with the initialization parameter OS_ROLES . If you choose to grant roles to users through operating system facilities, then you cannot also grant roles to users with the GRANT statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles.
Note on Oracle Automatic Storage Management
A user authenticated AS SYSASM can use this statement to grant the system privileges SYSASM , SYSOPER , and SYSDBA to a user in the Oracle ASM password file of the current node.
Note on Editionable Objects
A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
REVOKE for information on revoking grants
To grant a system privilege , one of the following conditions must be met:
You must have been granted the GRANT ANY PRIVILEGE system privilege. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted does not have the privilege unless the role is enabled in user's session.
You must have been granted the system privilege with the ADMIN OPTION . In this case, if you grant the system privilege to a role, then a user to whom the role has been granted has the privilege regardless whether the role is enabled in the user's session.
To grant a role to a user or another role , you must have been directly granted the role with the ADMIN OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in your own schema , you must have been directly granted the role with either the ADMIN OPTION or the DELEGATE OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in another user's schema , you must be the user SYS and the role must have been created by the schema owner or directly granted to the schema owner.
To grant an object privilege on a user , by specifying the ON USER clause of the on_object_clause , you must be the user on whom the privilege is granted, or you must have been granted the object privilege on that user with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you can grant an object privilege on a user only because you have the GRANT ANY OBJECT PRIVILEGE , then the GRANTOR column of the *_TAB_PRIVS views displays the user on whom the privilege is granted rather than the user who issued the GRANT statement.
To grant an object privilege on all other types of objects , you must own the object, or the owner of the object must have granted you the object privileges with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE , then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the *_TAB_PRIVS views displays the object owner rather than the user who issued the GRANT statement.
To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL , the current container must be the root.
You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.
Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:
Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process
Grant object privileges on the table
Create the indexes, integrity constraints, and triggers on the table
Specify the storage parameters of the table
Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.
DELETE and DROP TABLE for information on other ways of removing data from a table
TRUNCATE CLUSTER for information on truncating a cluster
To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.
To specify the CASCADE clause, all affected child tables must be in your schema or you must have the DROP ANY TABLE system privilege.
You can truncate a private temporary table with the existing TRUNCATE TABLE command. Truncating a private temporary table will not commit and existing transaction. This applies to both transaction-specific and session-specific private temporary tables. Note that a truncated private temporary table will not go into the RECYCLEBIN .
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema , then Oracle Database assumes the table is in your own schema.
You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.
Oracle Database changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.
Oracle Database also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table : range and hash partitions of local indexes and subpartitions of local indexes.
If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment. This operation is equivalent to creating a new segment for the index. Therefore, at the end of the truncate operation, the indexes are once again USABLE .
For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.
If a regular or index-organized table contains LOB columns, then all LOB data and LOB index segments are truncated.
If table is partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log. If this direct-path INSERT information is removed, then an incremental refresh of the materialized view may lose data.
All cursors are invalidated.
Restrictions on Truncating Tables
This statement is subject to the following restrictions:
You cannot roll back a TRUNCATE TABLE statement.
You cannot flash back to the state of the table before the truncate operation.
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
You cannot truncate the parent table of an enabled foreign key constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
If a domain index is defined on table , then neither the index nor any index partitions can be marked IN_PROGRESS .
You cannot truncate the parent table of a reference-partitioned table. You must first drop the reference-partitioned child table.
You cannot truncate a duplicated table.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export or import without affecting the ability of primary key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary key materialized views, the materialized view log must record primary key information.
The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.
Specify PRESERVE if any materialized view log should be preserved when the master table is truncated. This is the default.
Specify PURGE if any materialized view log should be purged when the master table is truncated.
Oracle Database Administrator’s Guide for more information about materialized view logs and the TRUNCATE statement
The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause, DROP ALL STORAGE clause, and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.
Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.
DROP ALL STORAGE
Specify DROP ALL STORAGE to deallocate all space from the deleted rows from the table, including the space allocated by the MINEXTENTS parameter. All segments for the table, as well as all segments for its dependent objects, will be deallocated.
Restrictions on DROP ALL STORAGE
This clause is subject to the same restrictions as described in "Restrictions on Deferred Segment Creation" .
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.
This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future.
This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.
If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
If you specify CASCADE , then Oracle Database truncates all child tables that reference table with an enabled ON DELETE CASCADE referential constraint. This is a recursive operation that will truncate all child tables, granchild tables, and so on, using the specified options.
Truncating a Table: Example
The following statement removes all rows from a hypothetical copy of the sample table hr.employees and returns the freed space to the tablespace containing employees :
The preceding statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.
Preserving Materialized View Logs After Truncate: Example
The following statements are examples of TRUNCATE statements that preserve materialized view logs:
-Where is the name of the table and you must be the owner of the table or have Drop any TABLE system privileges to truncate a table
-Storage is dropped by default if even not specified. If you want to preserve space, you can keep storage, then reuse storage
If you are truncating the table of another schema, use like this
3) Using Oracle GRANT to assign privileges which has ANY option example
Some system privileges have the keyword ANY that enables a user to perform the corresponding action on any objects in the database.
For example, SELECT ANY TABLE allows a user to select data from any table in any schema in the database.
Consider the following example.
First, log in as jack and select the data from t1 table in the john ‘s schema:
Oracle issued the following error:
Second, login as ot and grant the SELECT ANY TABLE system privilege to jack :
Third, from the session of john , execute the SELECT statement:
Here is the output:
Now the user jack can select data from any table in any schema in the Oracle database.
Object privileges
Object privileges decide how a user can access the data in the database. The object privileges apply to rows in tables or views.
Here are some common object privileges:
To grant one or more privileges to a user, you use the GRANT statement
How to grant truncate table in oracle
There is no truncate table privilege in Oracle. You need to provide Drop any table privilege to grant truncate table in oracle. Drop any table comes with many other privileges. So, this may not be possible in all cases. You can overcome this challenge by creating a procedure and granting execute on that procedure. Let’s understand with the example
Suppose you want to give a truncate table of one user USER1 to another user USER2
If you try to truncate the table simply, then you will hit the Error
Now let’s try to do this thing through the procedure and granting privilege on it
If you don’t want to do this thing, then you will need to grant drop any table privilege
1) Use Oracle GRANT to grant system and object privileges to a user example
In this tutorial, we will launch two SQL*Plus sessions, one for the user ot that will grant privileges and another for the user john .
First, launch SQL*Plus and log in to the Oracle database using the user john . Note that we assigned the user john the CREATE SESSION system privilege, so it should be able to log in.
In case you’re not following the CREATE USER tutorial, you can create the user john and grant the CREATE SESSION system privilege by using the following statements:
Second, use the user john to log in to the Oracle Database and create a new table:
Oracle issued the following error:
To allow the user john to create the table, you need to grant the CREATE TABLE system privilege to the user as shown in the following statement:
Now, the user john can create a new table:
The following statement shows the privileges of the current user:
Here are the privileges of the user john :
Third, use the user john to insert a new row into the t1 table:
Oracle issued the following error:
This is because the user john has a quota of zero on the USERS tablespace.
To fix this, you use the ALTER USER command to change the quota of the user john on the USERS tablespace:
Now, the user john should be able to insert a row into the t1 table:
And query data from the t1 table as well:
Here is the output:
Oracle GRANT statement examples
Let’s practice with the GRANT statement to get a better understanding.
Introduction to the Oracle GRANT statement
The GRANT statement assigns one or more privileges to a specific user. The following illustrates the basic syntax of the GRANT statement:
First, specify the system or object privileges that you want to assign to a user after the GRANT keyword. If you assign more than one privilege, you use a comma-separated list of privileges.
Second, specify the user that receives the privileges after the TO keyword.
Third, optionally use the WITH ADMIN OPTION if you want the user to be able to perform the following:
- Grant / revoke the privilege to / from another user.
- Alter the privilege to change the authorization needed to access it.
- Drop the privilege.
The user who receives the privileges via the GRANT statement is also known as a grantee.
Note that the GRANT statement also works with roles, which we will cover in the subsequent tutorial.
2) Use Oracle GRANT to assign privileges WITH ADMIN OPTION example
First, create a new user called jack and grant the user the CREATE SESSION so that the user can log in:
Second, grant the CREATE TABLE system privilege to john , but this time, use the WITH ADMIN OPTION :
Now, the user john can grant the CREATE TABLE system privilege to another user e.g. jack .
Third, login as john and grant the CREATE TABLE system privilege to jack :
Finally, login as jack and create a new table:
The user jack can create the table.
System privileges
System privileges determine what a user can do in the database. They mainly allow a user to add or modify schema objects in the database like creating tables, creating views, and removing tablespaces.
- CREATE SESSION
- CREATE TABLE
- CREATE VIEW
- CREATE PROCEDURE
- SYSDBA
- SYSOPER
What is a privilege?
By definition, a privilege is a right to execute an SQL statement or a right to access an object of another user.
Oracle defines two main types of privileges: system privileges and object privileges
The overview of Oracle privileges
After creating a user, you need to decide which actions the user can do in the Oracle database.
In the CREATE USER tutorial, we used the GRANT statement to provide the user john the CREATE SESSION system privilege to enable the user to log in the Oracle database.
Читайте также: