Grant Select Privilege on SELECT_CATALOG_ROLE to User

In Oracle, after the new user or schema is created, the user cannot query the data dictionary. For example, if you run the below query:

Select * from dba_data_files

you'll see error message as ORA-00942: table or view does not exist

To allow the user to select table from Oracle data dictionary, you have to grant SELECT_CATALOG_ROLE privilege the that users. Use the following statement while logging in as sysdba

sql> grand SELECT_CATALOG_ROLE to user_name;

Amazon Product List Page

Click here to check out product list

Shop Amazon Books - New J.K. Rowling - The Casual Vacancy

Retrieve Table Information in MS SQL Server

Quite often there is a need to check number of records in the table. It can be done with ease with the following SQL statement

Select Count(*) from TableName

However, it would have been better if we can retrieve number of records from the table with other information related to that table. SQL Server provides this store procedure to accomplish this task

sp_spaceused 'Table_Name'

more detail inside this book

The statement above shall return result in the table format. This SQL statement only works with one table in the chosen database. If we want to loop through all tables in the database to display table information, we can perform this step.

• First, create the temporary table to store result from store procedure
• Second, run the store procedure
• Last, use the select statement to display the result

Use the below SQL statement to create the temporary table in SQL Server

CREATE TABLE #tableSize(
TAB_NAME NVARCHAR(128),
RowsNO VARCHAR(30),
sizereserved VARCHAR(30),
sizedata VARCHAR(30),
index_size VARCHAR(30),
unused VARCHAR(30) )

Then, execute the store procedure below

INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

After the the procedure finish running, simply run the select statement.

SELECT * FROM #tableSize order by TAB_NAME

Find more books related to MS SQL Server

How to access table in different schema in Store Procedure

On one Oracle database, there are several users or schemas. Each schema contains tables. These table belong to each user. It is possible for a user to access table in other schema. For instant, if there are three users in Oracle database, such as USER1, USER2, and USER3.

In event that the USER3 needs to access the table which belongs to the USER2. SQL select statement is written as

Select Col1, Col2, Col3 from USER2.TableName

This statement works fine when the SQL statement is run in Toad or SQL-Plus. However, if the SQL statement above is put into the store procedure, it will not work. The store procedure will not compiled correctly.

To correct this problem, the USER2 has to grant select on table privilege the USER3. Follow the step below:

1. Login to the Oracle database as USER2
2. Run this statement Grant Select on USER2.TableName to USER3;

Now the USER3 can put the select statement of granted table in the store procedure.

Rename DATA FILE for Oracle

The data file is created when the Oracle DBA created the table space. If the Oracle DBA wants to rename the data file after the it has been created, he or she can do so by following these steps

1. The table space has to be taken offline; use the following command ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
2. Then data file is rename using the operating system.
3. After that the Oracle DBA renames the data file in SQL Plus enviroment. Supposed that the old data file name is 'USER1.DBF'. The new data file name is 'NEW_USER.DBF'. Following the step 2, issue this SQL comment ALTER TABLESPACE 'tablespace name' RENAME DATAFILE 'C:\USER1.DBF' to 'C:\NEW_USER.DBF'; This assumes that the data file is located in drive C
4. After rename the data file is performed in the Oracle environment, mount the tablespace online again, using this command: ALTER TABLESPACE tablespace_name ONLINE;

Now, the table space contain the data file with the new name.

How to undrop a table in Oracle

When a table in the Oracle databaseis dropped, table is not actually dropped. The dropped table is renamed to other name which starts with BIN$.... The table is stored in recycle area, so the table space is still occupied by the dropped table. This is the new feature in Oracle version 10G. We will try to create the new table, insert data, drop and undrop the table.

SQL> create table testing (col varchar2(10), row_chng_dt date);

Table created...

SQL> insert into testing values ('Version1', sysdate);

1 row created..

SQL> drop table testing;

Table dropped.

Now the deleted table is stored in the recycle area. To see the dropped table in the recycle area, issue this SQL commend.

SQL> select object_name, original_name, type, droptime

from recyclebin Where original_name = 'TESTING';

the result is shown below

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME

------------------------------ ------------- ----- ---------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TESTING TABLE 2006-09-01:16:10:12

If the user makes the select statement again, the user shall use BIN$HGnc55/7rRPgQPeM/qQoRw==$0 for table name

Select * from BIN$HGnc55/7rRPgQPeM/qQoRw==$0;

Now the user can restore the table by using this SQL statement

SQL> flashback table testing to before drop;

Flashback complete.

In case that we want to completely delete table Testing from database. We have to purge table from the recycle bin area.

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

Table purged.

After purging operation is complete, the table space in the Oracle is released.
Hone your skill on the Oracle database with this book

Creating DB User and Grant Option - Oracle

When the new user has been created in Oracle database, the new user cannot do anything with the database. The database admin has to grant privilege to that user. First privilege is

SQL> grant connect to Tommy;

Grant succeed.

The privilege allow the user Tommy to be able to connect to the database. In order for user Tommy to use the resource, granting resource must be follow:

SQL> grant resource to TOMMY;

Grant succeeded.

Now, the user Tommy can use resource like table space. There are other options in privilege to be granted. From my practice, after I create the new user, I also grant EXP_FULL_DATABASE to the user.

SQL> grant EXP_FULL_DATABASE to TOMMY;

This option allows the user to create and drop tables and other objects.