Coskan’s Approach to Oracle

December 6, 2007

Altering Without Privileges

Filed under: Basics — coskan @ 4:54 pm

There was a mail in Turkish Oracle Users group mailing list about “why a users can still alter a table despite all privileges has been revoked (only connect privilege is left). My answer was to double check if there is any privilege was left by selecting DBA_TAB_PRIVS for that user, but the best answer send by Yasin Baskan (Oracle Today). He claimed and proved that, if the objects are created/owned by that user he can still alter the objects without any privilege. Below is how he proved his claim and a little addition from me (privileges on different schema test).

First create a user with connect and dba privileges.

usercreate

Create tables with that user under his own schema;

create table

Create tables with that user under HR schema;

create table on hr

Revoke DBA privilage from user; (the costst user is must disconnect after revoke to connect with new privs)

revoke

Test ddl on table ;

ddl

He is still altering but can he create new ?

new

What about the table he created on HR schema, can he alter it ?He couldn’t as I expected because he doesn’t have access to that schema.

hrddl

What privileges user has ? (Checked by find_all_privs.sql script written by Pete Finnegan ). CONNECT and CREATE SESSION

privs

What if other one creates a table on coststs schema. He still has access  as you might guess

otheruser

The privileges comes from schema owning and this is the expected behaviour. You can not disable this option, so  you must think different ways  such as creating different user or moving tables under another schema, to prevent a user from  accessing tablesunder its schem.

Special thanks goes to Yasin  for this entry.

December 5, 2007

Temp Segments in Normal Datafile (ORA-1652)

Filed under: Basics, Tips, Uncategorized — coskan @ 6:44 pm

It has been over 2 months since my last entry, but i have reasons to be offline, like major location and job changes. I moved to UK (London) 2 months ago and joined to a huge organization. I am not just an Oracle DBA anymore, so you should see some SQL Server posts in this blog, please do not panic when you see them :) I am still in an endless love with Oracle.

First of all, I am sorry that I can’t write any adventures about UKOUG because one of my colleagues already arranged his attendance when I joined the company. I hope I will write my own adventures next year.

Now its time to write technical stuff. Today we faced “ORA-1652: unable to extend temp segment”. One of my team members asked my opinion about “why the user getting this error on a normal tablespace instead of TEMP tablespace”. He said, the user was trying to create a table by using “create table xxx as select” clause. At first I thought that this should not happen, but when I re-think, I realized that, this is behaviour is like index rebuild. “Create table as select” must be different then “insert into” clause. In my opinion, It first uses temp segments on a permanent tablespace and if it is successful then it converts the segments as permanent. (Updated: Metalink note 181132.1 Thanks YAS) Lets see how

User HR using USERS tablespace as default tablespace and TEMP as temporary tablespace;

tablespace

Size of the source table T1 is 264MB;

tablesize

Available free space on USERS tablespace is;

free space

Auto Extend option on USERS tablespace is off;

autoextend

So I don’t have enough space for new table T2. Lets see what error is raised with different clauses.

When I try to create table T2 from T1 I got error 01652 which is about using temp segment on default tablespace USERS.

unable to extent temp segment

When I try to insert data to T2 from T1 I got error 01653 which is about cannot allocate normal extent on default tablespace. (where 1=2 syntax is just for creating the same table structure without data)
normal segments

Lets try it about with index rebuild. As you might know you need free space with the same size of index when you to rebuld it.

First set auto extend on with 16MB extents;

Then create an index on T1;

create index

Size of the index is 43MB;

index size

Set auto extent off while free space on USERS tablespace is 6 MB;


autoext

When I tried to rebuild the index I expect to get temp segment error ORA-10252 on normal USERS tablespace, because it is really a temp usage.

rebuild

Moral of the story is, temp segments are not always located on TEMP tablespaces.

updated (11/12/2007): Taken from metalink note id 181132.1

4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require 
temporary segments: 
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
    --> The CTAS creates a data segment in the target tablespace and marks this 
        segment as temporary in dictionary. On completion, the dictionary type 
        is changed from temporary to table. In addition, if the SELECT performs 
        a SORT operation, temporary space may be used as for a standard select.
    --> For a Parallel CTAS statement, each slave builds its own data segment 
        (marked as temporary in the dictionary) from the row source which feeds
        it. 
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
    The CREATE INDEX statement, after sorting the index values, builds a 
    temporary segment in the INDEX tablespace; once the index is completely
    built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
    During an index rebuild, besides the temporary segments used to store 
    partial sort (segments built in the user's default TEMPORARY tablespace), 
    Oracle uses a segment which is defined as a temporary segment until the 
    rebuild is complete. 
    Once this segment is fully populated, the old index can be dropped and the 
    temporary segment is redefined as a permanent segment with the index name. 
    The new version of the index, currently a temporary segment, resides in the 
    tablespace where the index is required. Note that the old index segment that
    is to be dropped is itself converted to a temporary segment first (like drop
    ping a table). Therefore, an index rebuild involves three temporary segments,
    one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
    Oracle first converts the segment to a temporary segment, and starts 
    cleaning up the now temporary segments extents. If the drop is interrupted, 
    the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
    shutdown abort, this may cause serious problem, and the total time to
    cleanup is increased.

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers