creating oracle tablespace and quotasize dynamically [message #611280] |
Tue, 01 April 2014 02:05 |
|
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 &"a_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 #611302 is a reply to message #611293] |
Tue, 01 April 2014 03:09 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
santhosha4db wrote on Tue, 01 April 2014 08:41sir 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 #611311 is a reply to message #611310] |
Tue, 01 April 2014 03:36 |
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 #611315 is a reply to message #611308] |
Tue, 01 April 2014 03:48 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
santhosha4db wrote on Tue, 01 April 2014 09:30our 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>
|
|
|
|
|