GRANT ANY OBJECT PRIVILEGE

Real world scenario:

I have users: A_DBA, B_JUNIOR, C_MAIN, D_USERS.

I want my junior, user B_JUNIOR,  to grant execute privilege on C_MAIN.testProc to D_USERS, without having him execute privilege on C_MAIN.testProc.

So I want my junior to grant(JUST grant) object privilege on one schema to another schema .

Solution:

GRANT ANY OBJECT PRIVILEGE  is the privilege which solves it.

SQL> connect A_DBA/ta@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

Connected as A_DBA

SQL> grant GRANT ANY OBJECT PRIVILEGE to B_JUNIOR.

Grant succeeded

SQL> connect B_JUNIOR/a@MYDB

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

Connected as B_JUNIOR 

SQL> grant execute on C_MAIN.testProc to D_USERS

Grant succeeded

————————————BUT, junior can’t execute itself

SQL> exec C_MAIN.testProc;

begin C_MAIN.testProc; end;

ORA-06550: line 2, column 7:

PLS-00201: identifier 'C_MAIN.TESTPROC' must be declared

ORA-06550: line 2, column 7:

PL/SQL: Statement ignored

Resize Datafile to Reduce Gaps and release free space for others-Oracle

If there are many inserts and deletes in the database, some datafile becomes gapped.

Which means, the following:

X is busy block and 0 is free block

Picture 1.

Block Alocation in Datafile(Gapped Datafile)1

You can determine how much free space is in datafile:

SQL> SELECT a.file_id
  2        ,b.file_name
  3        ,SUM(a.bytes)/1024/1024 AS "Free MB"
  4  FROM dba_free_space a
  5      ,dba_data_files b
  6  WHERE a.file_id=b.file_id
  7  GROUP BY a.file_id,b.file_name
  8  ORDER BY 3 DESC
  9  /

   FILE_ID FILE_NAME                                            Free MB
---------- ---------------------------------------------------- ----------
         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF  914.5
         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF   382.4375
         6 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TESTTBS.DBF    19.9375
         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF   13.625
         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF  3.625
         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF    2.1875

6 rows selected

As you can see UNDOTBS01.DBF and SYSTEM01.DBF have biggest free spaces, but it’s better not to touch them.

Let’s choose TESTTBS.DBF datafile.

Calculate the total size of this datafile:

SQL> SELECT file_id,bytes/1024/1024 AS "Size MB"
  2  FROM dba_data_files
  3  WHERE file_id=6
  4  /

   FILE_ID    Size MB
---------- ----------
         6         20

Used Size Calculation: 20MB(Total)-19MB(Free)=1MB(Used)

SQL> ALTER DATABASE DATAFILE 6 RESIZE 1M;

Database altered

Everything is OK, but there may happen the following situation

Let’s choose another datafile EXAMPLE01.DBF and see the total size of it:

SQL> SELECT file_id,bytes/1024/1024 AS "Size MB"
  2      FROM dba_data_files
  3      WHERE file_id=5
  4  /

   FILE_ID    Size MB
---------- ----------
         5         81

Used Size Calculation: 81MB(Total)-3MB(Free)=78MB(Used)

SQL> ALTER DATABASE DATAFILE 5 RESIZE 78M;

ALTER DATABASE DATAFILE 5 RESIZE 78M

ORA-03297: file contains used data beyond requested RESIZE value

This shows that EXAMPLE01.DBF datafile is gapped, as it is shown on the Picture 1.

Let’s see what blocks are free:

SQL> SELECT file_id
  2        ,block_id
  3        ,bytes
  4  FROM dba_free_space
  5  WHERE file_id=5
  6  /

   FILE_ID   BLOCK_ID      BYTES
---------- ---------- ----------
         5        425    2490368
         5       1313     458752
         5      10265     851968

Picture describing the current situation:

Block Alocation in Datafile(Gapped Datafile)2

Size Calculation: 81(Total size MB)*1024*1024=84934656(Bytes)
In block 10265-free space is 851968(Bytes)
84934656(Bytes)-851968(Bytes)=84082688(Bytes)

 

SQL>  ALTER DATABASE DATAFILE 5 RESIZE 84082688;

Database altered

 

Now our datafile looks like that:

Block Alocation in Datafile(Gapped Datafile)3

If this is not enough and you still want to reduce datafile size to be just used space in it, then you may do the following:

Identify objects that are located in the blocks from 425, drop them, reduce datafile size till 424 block, recreate objects…not good yes?!Smile

The steps described in this post would be very useful if there was a lot of(or only) free space after the last block(In our case after 10264).

The best way it to create new datafile with the actual used size in your gapped datafile, export objects from old one and then import these objects to the new file.

The moral of this post is to show the logic, in what situation these steps are useful and what is the alternative solution.

I hope it was helpful for youSmile

Moving Tables with LOB columns to a diff. tablespace

In the previous post I discussed how to move table and its indexes to a different tablespace, and I also mentioned that moving tables with LOB columns to a different tablespace is performed by the different way. So here it is how to do this.

First of all, let’s create a test table with LOB column.

CREATE TABLE TST_TABLE(
COL_ID          NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
COL_CLOB   CLOB
);

–Insert one row into this table

INSERT INTO TST_TABLE
VALUES(1,’FIRST ROW’);
COMMIT;

–Query it

SELECT * FROM TST_TABLE

–Result

COL_ID   | COL_CLOB
——————————-
1              | <CLOB>

Now let’s check table’s current tablespace.

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————
TST_TABLE     | SYSTEM

 

Assume that we have already created a test tablespace, let’s say it is called TST_TBS.

–Move table to TST_TBS tablespace

ALTER TABLE TST_TABLE MOVE TABLESPACE TST_TBS

–Let’s check the result

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
————————————————–
TST_TABLE     | TST_TBS

When you create a table with LOB column, oracle automatically creates LOB segment and LOB index. If you do not indicate their(LOB segment and LOB index) names and where they  should be kept, oracle names them itself and creates in the same tablespace as the table is.

By default, segment name starts with SYS_LOB… and index name starts with SYS_IL…

Let’s check what indexes are already created for our table.

SELECT INDEX_NAME                 AS INDX_NM
,INDEX_TYPE                 AS  INDX_TP
,TABLESPACE_NAME   AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                      | INDX_TP | TBS_NM | STATUS
—————————————————————————————–
SYS_IL0000055912C00002$$| LOB         | SYSTEM  | VALID
TEST_PK                                      | NORMAL | SYSTEM  | UNUSABLE

Or you can query the following to identify segment name and index name.

SELECT COLUMN_NAME           AS CLN_NM
,SEGMENT_NAME        AS SEGMENT
,TABLESPACE_NAME  AS TBS_NM
,INDEX_NAME                AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         | TBS_NM | INDX_NM
———————————————————————————————————————-
COL_CLOB|SYS_LOB0000055912C00002$$| SYSTEM |SYS_IL0000055912C00002$$

Because of, by default LOB column is stored outside of the table it is still located in the SYSTEM tablespace(was not moved).

In order to move LOB column to a different tablespace, you should run the following command.

ALTER TABLE TST_TABLE MOVE LOB(COL_CLOB)
STORE AS (TABLESPACE TST_TBS);

–Check it

SELECT COLUMN_NAME         AS CLN_NM
,SEGMENT_NAME       AS SEGMENT
,TABLESPACE_NAME AS TBS_NM
,INDEX_NAME               AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         |TBS_NM  | INDX_NM
———————————————————————————————————————–
COL_CLOB|SYS_LOB0000055912C00002$$|TST_TBS |SYS_IL0000055912C00002$$

Let’s query the following:

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | SYSTEM  | UNUSABLE

As you can see, LOB index was automatically moved to the new tablespace, but normal index still is in the same tablespace. To move it to a different tablespace, run the following:

ALTER INDEX TEST_PK REBUILD TABLESPACE TST_TBS

–Check it

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | TST_TBS |  VALID

 

After rebuilding an index, it became valid. As I also said in the previous post , if at least one index of the table is not valid DML operations will fail.

Moving tables and indexes to a different tablespace

Moving table to a different tablespace can be done by several ways:

  1. Using Export/Import.
  2. Or by the following clause:

ALTER TABLE schemaName.tableName REBUILD TABLESPACE tablespaceName;

I will discuss the second variant now.

–Let’s create a test table.

CREATE TABLE TESTTABLE(
TESTCOLUMN NUMBER CONSTRAINT TEST_PK PRIMARY KEY
);

–Insert one row into that table

INSERT INTO TESTTABLE
VALUES(1);
COMMIT;

–Let’s see rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoRAABAAAPVSAAA  | 1

Before you move table to a different tablespace, you must have created  this tablespace before.

–Now let’s create a test tablespace.

CREATE TABLESPACE testTablespace
DATAFILE   ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\testTablespace01.DBF’
SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO

–Let’s see table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | SYSTEM

–Move this table to a test tablespace

ALTER TABLE TESTTABLE MOVE TABLESPACE TESTTABLESPACE;

–See again table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | TESTTABLESPACE

–Let’s see again rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoSAAHAAAAAMAAA | 1

As you can see rowid was changed. It makes indexes of this table unusable. To check it, run the following query.

SELECT INDEX_NAME,TABLE_NAME,STATUS,TABLESPACE_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

INDEX_NAME | TABLE_NAME | STATUS       | TABLESPACE_NAME
—————————————————————————————–
TEST_PK         | TESTTABLE    | UNUSABLE | SYSTEM

Any DML operation on this table will cause an error, because an index is not valid. Let’s check it:

INSERT INTO TESTTABLE
VALUES(2);
COMMIT;

–Error

ORA-01502: index ‘SYS.TEST_PK’ or partition of such index is in unusable state

This happens because you have moved test table to another tablespace, and this operation changed rowid value. The index still refers to the old rowid value. To solve this, you must rebuild the index.

ALTER INDEX TEST_PK REBUILD TABLESPACE TESTTABLESPACE;

This command will rebuild the index and also moves it to TESTTABLESPACE tablespace. If you want to rebuild index without moving it to another tablespace. Simply, run the following command:

ALTER INDEX TEST_PK REBUILD;

–Let’s check its status

SELECT INDEX_NAME,TABLE_NAME,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’

–Result

INDEX_NAME | TABLE_NAME | STATUS  | TABLESPACE_NAME
—————————————————————————————
TEST_PK         | TESTTABLE    | VALID       | TESTTABLESPACE

You must do this action for each index, to make DML operation resumable on this table.

Note that, if table contains LOB columns this variant doesn’t work. Moving tables with LOB columns to another tablespace is discussed here.

Identifying Unused Objects in Database

Sometimes in the database, there exist objects that have never been used. As you can guess they are junk objects and are waisting necessary space in the database. So we should identify them and remove from the DB. Oracle offers us some useful techniques to do this.

First of all, you should turn on monitoring process on that objects that are the most candidates of junk.

For example:

Assume that index sal_indx is considered to be the object, which has never been used. For to identify it, let’s turn on monitoring on that index.

–In your schema

alter index sal_indx monitoring usage;

–In other schema

alter index schemaName.sal_indx monitoring usage;

To check if it is monitored, query the following view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | NO   | 02/13/2011 21:48:25

Note: the result shows that this index has not been used since we tuned on monitoring, but it still doesn’t mean that this object is junk. It depends on what is the maximum period of time in which this object should be used. For example, if you know that this object should be used minimum once in a day, let it to be monitored till tomorrow, then query again v$object_usage view and you will see if this object has been used since yesterday or not and identify its necessity.

For example, someone run the following query:

select *
from salary
where id=1;

Assume that index sal_indx is created on id column of salary table.

–Check again the v$object_usage view

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | YES | YES  | 02/13/2011 21:48:25

From here you can identify that this index is necessary and should not be removed from database.

Turning Off Monitoring

–In your schema

alter index sal_indx nomonitoring usage;

–In other schema

alter index schemaName.sal_indx nomonitoring usage;

–Check if the monitoring is disabled

select index_name
       ,table_name
       ,monitoring as mon
       ,used
       ,start_monitoring
from v$object_usage;

–The Result

INDEX_NAME | TABLE_NAME | MON | USED | START_MONITORING
-----------------------------------------------------------
SAL_INDX   | SALARY     | NO  | YES  | 02/13/2011 21:48:25