Re: Convertion ORACLE query to MYSQL

2006-11-09 Thread Grant Allen 

On 11/9/06, ViSolve DB Team <[EMAIL PROTECTED]> wrote:

Hi Experts,

We are in the process of converting Oracle administration commands into MySQL 
for some purposes. We dont know how to convert the following Oracle commands to 
MySQL. How to do that..?

1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;


There's no equivalent concept in MySQL to a temp tablespace.  The key
with them in Oracle is actions performed in a temp tablespace aren't
logged (sorts and hashes that overflow the memory), and the files that
constitute them aren't required for recovery.


2. DROP USER jbossjms1 CASCADE;


No equivalent to the cascade option ... so create a script-generating
script, or drop the database if you've equated schema with database.


3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;


You normally don't need to muck around with a storage engine's
low-level tablespace management.  You might want to work out what
objects this would drop, and do that instead.


4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE 
AUTOEXTEND ON MAXSIZE UNLIMITED;


Ensure the files in your innodb_data_file_path have the autoextend
property set, and total atleast 100MB in size.  The Oracle syntax
above uses ASM-based storage, which has no equivalent in MySQL, so
forget that bit


5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;


As per normal user creation ... just ignore the tablespace bit.

Ciao
Fuzzy
:-)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Convertion ORACLE query to MYSQL

2006-11-09 Thread Anders Karlsson 
These commands are Oracle specific or contains Oracle specific 
extensions. Nothing wrong with that, but in some cases there just is no 
corresponding command in MySQL, as the concepts are different.


ViSolve DB Team wrote:

Hi Experts,

We are in the process of converting Oracle administration commands into MySQL 
for some purposes. We dont know how to convert the following Oracle commands to 
MySQL. How to do that..?

1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
  
In MySQL there are no tablespaces in general, although certain storage 
engines use them. But they have completely different properties from 
what is the case with Oracle. Also, there is no such thing as a specific 
temporary tablespace.

2. DROP USER jbossjms1 CASCADE;
  
DROP USER works fine in MySQL. In Oracle, there is a specific connection 
between a User and the Schema, or rather, they are the same. In MySQL, 
these are different, there is a schema (or in MySQL, a database) and 
then there is granted access to that schema, that is it, there is no 
specific ownership of a schema. Assuming you set up MySQL the same way 
as Oracle, that each user (jbossjms1) gets his own schema (jbossjms1) 
and you want to drop both of those, in MySQL you would:

DROP DATABASE jbossjms1;
DROP USER jbossjms1;

3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
  
As there is a DROP TABLESPACE command in MySQL 5.1, but that does not 
with all certainty do what you want it to. The closest command is 
probably DROP DATABASE, but that assumes that you have all the jbossjms1 
objects in that database. I'd be careful here though, and read up on 
these commands in both Oracle and MySQL before you do this, as this 
might, and again might not, do what you want. These are admin commands, 
which typically work differently in different RDBMS systems.

4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE 
AUTOEXTEND ON MAXSIZE UNLIMITED;
  
This is again an administration commend. Assuming you are using the 
InnoDB storage engine, this command corresponds to 
"innodb_data_file_path" setting in the MySQL configuration file (my.cnf 
/ my.ini etc).

5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
  
This command works similarly same in MySQL, with the exception that 
there is no concept of a DEFAULT TABLESPACE in MySQL.

Thanks in Advance,

ViSolve PlanCAT Team

  


--
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /  Anders Karlsson ([EMAIL PROTECTED])
 / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/  /_/\_, /___/\___\_\___/ Stockholm
   <___/   www.mysql.com Cellphone: +46 708 608121



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Convertion ORACLE query to MYSQL

2006-11-09 Thread ViSolve DB Team 
Hi Experts,

We are in the process of converting Oracle administration commands into MySQL 
for some purposes. We dont know how to convert the following Oracle commands to 
MySQL. How to do that..?

1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;

2. DROP USER jbossjms1 CASCADE;

3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;

4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE 
AUTOEXTEND ON MAXSIZE UNLIMITED;

5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;

Thanks in Advance,

ViSolve PlanCAT Team