Home » SQL & PL/SQL » SQL & PL/SQL » creating oracle tablespace and quotasize dynamically (ORACLE 11g)
creating oracle tablespace and quotasize dynamically [message #611280] Tue, 01 April 2014 02:05 Go to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
I have created a sql script, In that i need to add tablespace and quota size dynamically. for example i have three tablespace 'abc,'def',ghi' for each tablespace i need to specify different quotas means for tablespace 'abc' i need to specify 1m quota, and def unlimited so on. below is my script, i have used utl_file which contains tablespace names. but i am not able specify quota size dynamically any help would be appreciated. Below is my code.


WHENEVER SQLERROR EXIT;

set show off;
set verify off;
set feedback off;
set serveroutput on;

DECLARE
u_file UTL_FILE.FILE_TYPE;
u_line VARCHAR(32767);
u_count INTEGER := 0;
TYPE my_arr1 IS TABLE OF VARCHAR2(100);
objTablespace my_arr1 := my_arr1();

BEGIN
SELECT COUNT (1) INTO u_count FROM dba_users WHERE username = UPPER ('&&username');
IF u_count != 0
THEN
dbms_output.put_line('User already exisits,Try another..!');
RETURN;
ELSE
EXECUTE IMMEDIATE 'create user &&username identified by &&password default tablespace &&default_tablespace temporary tablespace &&temp_tablespace';
EXECUTE IMMEDIATE 'grant dba to &&username';
EXECUTE IMMEDIATE 'revoke unlimited tablespace from &&username';

u_file := UTL_FILE.FOPEN('UTLFILEDIR','tablespacenames.txt','r',32767);
BEGIN
LOOP
UTL_FILE.GET_LINE(u_file, u_line, 32767);
objTablespace.EXTEND;
objTablespace(objTablespace.COUNT) := u_line;
END LOOP;
EXCEPTION
WHEN OTHERS THEN -- it actually should be no_data_found
UTL_FILE.FCLOSE(u_file);
END;

FOR i IN objTablespace.FIRST..objTablespace.LAST LOOP
EXECUTE IMMEDIATE 'alter user &&username quota &&quota_size on ' || objTablespace(i);
END LOOP;
END IF;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (' ');
END;
/
QUIT;
---------------------------

Where TablespaceNames.txt contains only tablespace names.
Re: creating oracle tablespace and quotasize dynamically [message #611281 is a reply to message #611280] Tue, 01 April 2014 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you exactly mean by "but i am not able specify quota size dynamically"?

Re: creating oracle tablespace and quotasize dynamically [message #611282 is a reply to message #611280] Tue, 01 April 2014 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also remove the following part:

Quote:
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (' ');


and read WHEN OTHERS.

Re: creating oracle tablespace and quotasize dynamically [message #611284 is a reply to message #611282] Tue, 01 April 2014 02:19 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
Hi Sir, tqs for reply/ In the above code tablespacenames.txt contains 3 tablespaces. for each tableapace i need to specify quota size at run time. in the code below i written alter user command it that i have to specify quota_size for each tablespace seperately. for ex. for tablespace abs i need to specify quota 1m, and tablespace def i need to specify quota unlimited like this it shoud ask at runtime.
Re: creating oracle tablespace and quotasize dynamically [message #611285 is a reply to message #611284] Tue, 01 April 2014 02:20 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
please help me sir. I am not getting any answer for this, i submitted to so many forums but no use.
Re: creating oracle tablespace and quotasize dynamically [message #611288 is a reply to message #611285] Tue, 01 April 2014 02:32 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
Anybody here to help me. please
Re: creating oracle tablespace and quotasize dynamically [message #611289 is a reply to message #611288] Tue, 01 April 2014 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First, be patient, we are not there waiting for your questions, we have other things to do.

If you want different quota for each tablespace then add the quota in your file.

Re: creating oracle tablespace and quotasize dynamically [message #611292 is a reply to message #611289] Tue, 01 April 2014 02:39 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
sorry sir.pls forgive me. if i wont submit it by today EOD i ll defenitely loose my job.
Re: creating oracle tablespace and quotasize dynamically [message #611293 is a reply to message #611292] Tue, 01 April 2014 02:41 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
sir quotas should ask at runtime for each tablespace, so user can specify quotas as per the requirements.
Re: creating oracle tablespace and quotasize dynamically [message #611302 is a reply to message #611293] Tue, 01 April 2014 03:09 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
santhosha4db wrote on Tue, 01 April 2014 08:41
sir quotas should ask at runtime for each tablespace, so user can specify quotas as per the requirements.
I can see what your code is trying to do, but to me it seems highly unlikely that it is what is actually required: I cannot imagine any circumstance where such code would go into production, which is what you are suggesting. Please can you post the requirements? In the original wording? Then perhaps someone can suggest a solution.
Re: creating oracle tablespace and quotasize dynamically [message #611308 is a reply to message #611302] Tue, 01 April 2014 03:30 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
our requirement is to create tablepaace and users outside the SQL*PLUS, so that the user can not touch the production database. the above code is working perfectly but i cannot able to specify quota for each tablespace which are in the TablespaceNames.txt. it ll asks quotas size for only one time, whatever user specify it is applying that quota size to all tablespaces names which are in utl-file. but i need to apply quota size differently for each tablespace.
Re: creating oracle tablespace and quotasize dynamically [message #611310 is a reply to message #611308] Tue, 01 April 2014 03:32 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
for example if Tablespacenames.txt containd 'n' number of names. then it should asks 'n' times to specify quota size for each 'n; number of names.
Re: creating oracle tablespace and quotasize dynamically [message #611311 is a reply to message #611310] Tue, 01 April 2014 03:36 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if it has to be "outside the SQL*Plus", how is the user calling that PL/SQL procedure then?

The thing is, PL/SQL runs *on the server* there is no way to interactively ask the user something from inside PL/SQL. You have to ask the user from whichever client application he is using to interact with the DB. If that isn't SQL*Plus, what is the client application?
Re: creating oracle tablespace and quotasize dynamically [message #611312 is a reply to message #611308] Tue, 01 April 2014 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is the way SQL*Plus (or other SQL client tool) works.
It asks for the values then send the PL/SQL block to the server to be executed.
As I said you have to put the quota in the file or in another file.
Or if you want to do it at client side then write your own code.

Note that:

1/ "revoke unlimited tablespace from &&username" is useless as your grant it DBA and so it implicitely has UNLIMITED tablespace even if you don't see it.
2/ So "alter user &&username quota &&quota_size on ' || objTablespace(i)" is useless as user has unlimited quota on all tablespaces.
3/ So your problem is "solved" as you don't need to ask for a quota. Razz

Re: creating oracle tablespace and quotasize dynamically [message #611314 is a reply to message #611312] Tue, 01 April 2014 03:47 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
@ThomsG, Sir, we are calling the above code(creation.sql) inside the shell script.
Re: creating oracle tablespace and quotasize dynamically [message #611315 is a reply to message #611308] Tue, 01 April 2014 03:48 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
santhosha4db wrote on Tue, 01 April 2014 09:30
our requirement is to create tablepaace and users outside the SQL*PLUS, so that the user can not touch the production database. the above code is working perfectly but i cannot able to specify quota for each tablespace which are in the TablespaceNames.txt. it ll asks quotas size for only one time, whatever user specify it is applying that quota size to all tablespaces names which are in utl-file. but i need to apply quota size differently for each tablespace.
What do you mean by "outside the SQL*PLUS"? And where do you "create tablepaace"? Please can you provide the requirement, in the original wording?

One point: if you trying to limit what your code can do, it does NOT prevent people from touching your production database: it is wide open to a SQL injection. For example, look at this example, where I give myself unlimitred quota on system:
orclz>
orclz> begin
  2  EXECUTE IMMEDIATE 'create user &&username identified by &&password default tablespace &&default_tablespace temporary tablespace &&temp_tablespace';
  3  end;
  4  /
Enter value for username: jw
Enter value for password: jw
Enter value for default_tablespace: users
Enter value for temp_tablespace: temp quota unlimited on system
old   2: EXECUTE IMMEDIATE 'create user &&username identified by &&password default tablespace &&default_tablespace temporary tabl
espace &&temp_tablespace';
new   2: EXECUTE IMMEDIATE 'create user jw identified by jw default tablespace users temporary tablespace temp quota unlimited on
system';

PL/SQL procedure successfully completed.

orclz>
 

Re: creating oracle tablespace and quotasize dynamically [message #611316 is a reply to message #611315] Tue, 01 April 2014 03:56 Go to previous messageGo to next message
santhosha4db
Messages: 10
Registered: April 2014
Location: BANGALORE
Junior Member
@John Watson. Sir, its correct. if we want to specify different quota size otherthan 'unlimited', means sometimes we need to specify 100mb or 200mb on some tablespace name, in that case again i have to change inside the code as above in your code. but i dont want to change it inside the code. it should ask quota size seperately for each tablepace which we mention in Tablespacenames.txt. For ex. i have three tablespace i want to specify diffrent quota size for each tablespace.
Re: creating oracle tablespace and quotasize dynamically [message #611323 is a reply to message #611316] Tue, 01 April 2014 04:59 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 01 April 2014 10:41

This is the way SQL*Plus (or other SQL client tool) works.
It asks for the values then send the PL/SQL block to the server to be executed.
As I said you have to put the quota in the file or in another file.
Or if you want to do it at client side then write your own code.

Note that:

1/ "revoke unlimited tablespace from &&username" is useless as your grant it DBA and so it implicitely has UNLIMITED tablespace even if you don't see it.
2/ So "alter user &&username quota &&quota_size on ' || objTablespace(i)" is useless as user has unlimited quota on all tablespaces.
3/ So your problem is "solved" as you don't need to ask for a quota. Razz



Previous Topic: Longest word PL SQL
Next Topic: VIEW with Global Variables - help needed
Goto Forum:
  


Current Time: Thu Apr 25 00:44:17 CDT 2024