A question about granting access to create temp tables 1

[ijroling]

I run a queries off of an Oracle database for reporting purposes, and I would like to be able to create temporary tables which contain data that can not be found on the server. I believe doing so would speed up the time needed to run my queries, which can often pull up to a million records at a time.

I've discovered that no users on this server have the proper permission to create temp tables though, and I was wondering if there is a reason for this. Do the DBAs out there know if it's possible to have permission to create temporary tables only without being given the rights to modify existing tables? Also, is this privilege not being given out to users because it may adversely affect server performance?

Thanks everyone!

 

[LKBrwnDBA]

The fact may be that if you "create temporary tables which contain data that can not be found on the server" then there is no guarantee for the reliability of the reported data.

In other words, how do they know the data you are using from those tables for the reports is valid and up to date? What is the source? As a receipient of the report I would be sceptical of such data.

On the other hand I see the same reason for preventing users from creating tables in their own schema.

My 2c






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ijroling]

Sorry, I guess I shouldn't have worded it like that. The data does exist on the database, but it is not identified in the way we need it to be. We have lists of specific values we are looking for out of millions of records, and it just isn't practical to list out a couple thousand values in a select statement. So what we end up doing right now is creating a query in Access (ugh), and join our custom table of values back to the database to get our results.

Needless to say, this takes incredibly long. If we could run an SQL statement that loads these values into a temporary table, we could use our PLSQL client to join that temp table to the database, which should speed up the query runtime by quite a bit. And when you have customers breathing down your throat for their data, the extra time you can get from running quick queries is priceless.

 

[jaxtell]

I think what you want is a materialized view. Are you familiar with them?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[ijroling]

I've never used them before, but I just looked up how, and once again, I don't have the rights to create one. Is there a reason why we wouldn't be given access? Does access to create this carry with it security or performance issues?

Thanks for your help!

 

[LKBrwnDBA]

In some corporations it is forbidden (aka. SOX) to create sensitive and/or financial reports based on data from spreadsheets and/or other intermediary source even if this data originated from a database.

Given your predicament, I see no reason to prevent a "super" user or developer to temporarily create tables with summary data from the database in their own schema.

On the other hand, maybe a business case could be built to request that these summary tables be added to the main schema perhaps as "Materialized Views" as jaxtel suggests.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ijroling]

Thanks LKBrwnDBA, I appreaciate the feedback. I think I feel comfortable going forward with this now.

 

[LKBrwnDBA]

Bottom line, it will all depend on "trust". If the receipient "Managers" want this information in a timely manner, they can help you in requesting adequate privileges to do your job.

Good luck!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb