Systems Engineering and RDBMS

Archive for October 17th, 2007

Adobe Integrated Runtime (AIR)

Posted by decipherinfosys on October 17, 2007

Adobe’s AIR or Adobe Integrated Runtime brings together PDF, Flex, Ajax, Javascript, Flash into a single framework. This would allow a single code base to be run both as a web application and as a desktop application on Windows, Mac and Linux.  AIR and WPF (Windows Presentation Foundation) from MSFT seem to be very similar.

You can get more information about AIR from Adobe’s site: http://labs.adobe.com/technologies/air/

Posted in Technology | Leave a Comment »

Read Only tablespaces

Posted by decipherinfosys on October 17, 2007

At times, we have to restrain users from updating certain data or inserting new data in certain tables. This can be achieved in a couple of different ways. We can give only ‘SELECT’ privilege to the user and revoke all other privileges from the user on the specific table. Another way of achieving this is to create a read only tablespace in which those table(s) reside. As we make tablespace itself read only, nobody will be able to update/delete or insert data into any of the table. Another good thing is that we can switch back tablespace to READ WRITE mode once we are done with our activity.  Let us start with creating tablespace and a table with some data in it. We are assuming that you have the correct privileges to issue the create tablespace command.

SQL> CREATE TABLESPACE TEST_TB
2  DATAFILE ‘C:\oracle\oradata\orcl\TEST_TB_01.DBF’ SIZE 500M
3  /

Tablespace created.

We are changing user’s default tablespace to TEST_TB. In our case username is decipher. It may be different for your scenario.

SQL> ALTER USER DECIPHER DEFAULT TABLESPACE TEST_TB
2  /

Now connect to user with proper credentials to create a table and populate it with some data.
SQL> CONNECT DECIPHER/DECIPHER@ORCL
Connected.

We can also verify user’s default tablespace by issuing following command.

SQL> SELECT DEFAULT_TABLESPACE FROM USER_USERS
2   WHERE USERNAME = ‘DECIPHER’;

DEFAULT_TABLESPACE
——————————
TEST_TB

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_NAME VARCHAR(30),
TEST_DATE DATE
)
/

INSERT INTO TEST(TEST_ID,TEST_NAME)
SELECT ROWNUM,OBJECT_NAME
FROM DBA_OBJECTS
WHERE ROWNUM <= 100
/

Now let us change the tablespace to read only by issuing ALTER TABLESPACE command.

SQL> ALTER TABLESPACE TEST_TB READ ONLY;

Tablespace altered.

Since tablespace is altered in READ ONLY mode, any DML operation on the table will fail with an error. Let us try to delete records from the table.

SQL> DELETE FROM TEST;
DELETE FROM TEST
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST_TB_01.DBF’

We will get the same error when we try to update or insert the records in the table. This is one of the ways to give READ ONLY access to specific group of people.  We can revert back the READ ONLY change and can make tablespace back to normal where we can make updates to the table. In order to make tablespace writable we have to issue following command.

SQL> ALTER TABLESPACE TEST_TB READ WRITE;

Tablespace altered.

Now performing delete or update on TEST table will go through without any errors.

SQL> DELETE FROM TEST;

100 rows deleted.

In fact we can check the status of the tablespace in USER_TABLESPACE to determine whether it is read only or not.

Posted in Oracle | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers