Since no where I go seems to have heard of Application Express, I have to demo it (locally) and then convince the DBAs to install it. As such, I do tell them that the APEX Administrator (account) has very powerful privileges, including CREATE TABLESPACE.
I don't think it ever truly registered what this means. In talking to my friend and (current) colleague Daniel McGhan (APEX nerd extraordinaire), he reminded me that when you create a new workspace in APEX that it creates a new tablespace as well (if you don't set "Re-user existing schema?" to "Yes") which means a datafile is also created.
Of course I had to see if for myself:
CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;That's what I currently have. I'll create a new workspace now to demonstrate. Starting from the Manage Workspace page I select Create Workspace:
FILE# NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/testing/system01.dbf
2 /u01/app/oracle/oradata/testing/sysaux01.dbf
3 /u01/app/oracle/oradata/testing/undotbs01.dbf
4 /u01/app/oracle/oradata/testing/users01.dbf
5 /u01/app/oracle/oradata/testing/example01.dbf
5 rows selected.
I give it a name, APEX_TEST:
On Step 2, I set "Re-user existing schema?" to "No"
Step 3:
Step 4, confirming details:
Confirmation page:
CJUSTICE@TESTING>SELECT file#, name FROM v$datafile;As you can see, the datafile FLOW_1172420773490155.dbf was created which is mapped to the like named FLOW_1172420773490155 tablespace.
FILE# NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/oradata/testing/system01.dbf
2 /u01/app/oracle/oradata/testing/sysaux01.dbf
3 /u01/app/oracle/oradata/testing/undotbs01.dbf
4 /u01/app/oracle/oradata/testing/users01.dbf
5 /u01/app/oracle/oradata/testing/example01.dbf
6 /u01/app/oracle/oradata/testing/FLOW_1172420773490155.dbf
6 rows selected.
This can obviously be controlled by selecting "Yes" for "Re-user existing schema?", but I just thought it important enough to point out.
6 comments:
Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!
Reusing an existing schema would be even more scary to the typical DBA. Say the DBA has set up his own DBA account separate to SYS, SYSTEM etc (such as BRAINIAC). Create an Apex Workspace for BRAINIAC and a developer in that workspace has access to SQL Worksheet as the BRAINIAC user (eg with DBA privileges).
And at no point do you need to know the BRAINIAC schema password.
There's a handful of hardcoded schemas it won't allow as the basis for a workspace, but in practice an Apex Admin would have no trouble gaining any privilege they want on the database.
@gary
Good point. That's definitely one that I do try to make when explaining APEX, that the workspace inherits the privileges of the schema it is mapped to. But it is not exact (and I haven't checked), but if you are a DBA, and want to use BRAINIACS as your schema, you can't create reports on the dictionary without direct privileges (can't remember off the top of my head). I've tried to do so in the past, albeit not from SQL Workshop.
Hi,
Is there a way to configure which tablespace should be used when reusing an existing schema?
Say for example I have a schema that will be used for creating 10 workspaces. If I understand this correct all the workspaces will be created in the default tablespace of the user. But what if I want to have a new tablespace/datafile for each workspace. How can I do this?
Thanks in advance.
@APEX_Newbie,
I've been through this a couple of times and I can't seem to find a place where you could specify the tablespace you wanted to use. I tried creating one from scratch, nada. I tried editing an already existing one, nada.
I'm sure there is a way, but it's beyond me and might actually be part of the internals of APEX (i.e. don't mess with or else you risk desupport of your environment).
Perhaps the 4.0 release will expose the ability to map to a tablespace, I haven't gotten a chance to look just yet.
chet
The workspace per se doesn't own any objects - the schema does. You can have three workspaces in the same schema all using the same tables.
With Worksheets, there will only be one set of APEX$_WS* tables in the schema.
The application metadata will all sit in the Apex schema tables.
When you create tables, you can always specify an explicit tablespace for them. You can cheat and give the schema a default tablespace that they don't have a quota on, so that they always have to specify a non-default tablespace for the tables.
Primarily the role of a schema is to separate groups of objects, so I'm not sure why you would create multiple workspaces in a single schema except to share objects.
Post a Comment