|
(→Altering Passwords)
|
(Reordering the data for easier flow)
|
| Line 1: | |||
| + | = Quick Reference SQL for Oracle XE = | ||
| + | I'm throwing a quick reference guide to some common SQL I have to use and can never seem to locate. I'm throwing them here instead. | ||
| + | == Initial User Creation == | ||
| + | This will create a username with a password, and grant the roles required out of the XE documentation: | ||
| + | <pre>create user testuser identified by testpassword; | ||
| + | grant connect, resource to testuser; | ||
| + | </pre> | ||
| + | == Get Current Tablespace Size == | ||
| + | If an error occurs that the tablespaces are too large, the current size can be checked with the following SQL: | ||
| + | <pre>select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes )/1024/1024 | ||
| + | from dba_extents a,dba_data_files b where a.file_id=b.file_id group by | ||
| + | a.file_id,b.file_name, autoextensible,b.bytes/1024/1024;</pre> | ||
| + | == Increase Tablespace Size == | ||
| + | To increase the amount of space, locate the file from the previous section and run the following to increase: | ||
| + | <pre>ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 2048M;</pre> | ||
| + | == Drop all Tables/Content Associated with Users == | ||
| + | Unlike other database types, Oracle doesn't have a concept of 'individual databases.' Instead, it uses one database with different users/views. If you drop a user, all content associated with them will be removed (equivalent to MySQL's DROP DATABASE). To perform this: | ||
| + | <pre>DROP USER testuser CASCADE;</pre> | ||
| + | == Altering Passwords == | ||
| + | Run the following to change the password of a user: | ||
| + | <pre>ALTER USER testuser IDENTIFIED BY newpassword;</pre> | ||
| + | == Data Pumping Export & Import == | ||
| + | The original documentation can be found at [http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm#BCEIHIHE http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm#BCEIHIHE], but posting here for quick reference. '''NOTE THAT SQL DEVELOPER DOES NOT SUPPORT EXPORT OF CLOB/BLOB TYPES''' SO EXPORT THROUGH THE COMMAND LINE TO A BINARY FORMAT IS PREFERRED. | ||
| + | Run the following command to setup a dump directory (if not already set up & assuming directory exists): | ||
| + | <pre>CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp'; | ||
| + | GRANT READ,WRITE ON DIRECTORY dmpdir TO testuser;</pre> | ||
| + | Then the export can be performed from the command prompt: | ||
| + | <pre>expdp SYSTEM/password SCHEMAS=testuser DIRECTORY=dmpdir DUMPFILE=testuserschema.dmp LOGFILE=exptestuserschema.log</pre> | ||
| + | An import to a new user can be performed as following without having to create the new user first: | ||
| + | <pre>impdp SYSTEM/password SCHEMAS=testuser DIRECTORY=dmpdir DUMPFILE=testuserschema.dmp | ||
| + | REMAP_SCHEMA=testuser:testuserdev EXCLUDE=constraint, ref_constraint, index | ||
| + | TABLE_EXISTS_ACTION=replace LOGFILE=imptestuserschema.log</pre> | ||
| = Test Install Run = | = Test Install Run = | ||
| Line 69: | |||
| [echo] http://localhost:80/ to complete the Installation. | [echo] http://localhost:80/ to complete the Installation. | ||
| [echo] Installer Complete. (press return to exit installer)</pre> | [echo] Installer Complete. (press return to exit installer)</pre> | ||
| - | = Quick Reference SQL for Oracle XE = | ||
| - | I'm throwing a quick reference guide to some common SQL I have to use and can never seem to locate. I'm throwing them here instead. | ||
| - | == Initial User Creation == | ||
| - | This will create a username with a password, and grant the roles required out of the XE documentation: | ||
| - | <pre>create user testuser identified by testpassword; | ||
| - | grant connect, resource to testuser; | ||
| - | </pre> | ||
| - | == Get Current Tablespace Size == | ||
| - | If an error occurs that the tablespaces are too large, the current size can be checked with the following SQL: | ||
| - | <pre>select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes )/1024/1024 | ||
| - | from dba_extents a,dba_data_files b where a.file_id=b.file_id group by | ||
| - | a.file_id,b.file_name, autoextensible,b.bytes/1024/1024;</pre> | ||
| - | == Increase Tablespace Size == | ||
| - | To increase the amount of space, locate the file from the previous section and run the following to increase: | ||
| - | <pre>ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 2048M;</pre> | ||
| - | == Drop all Tables/Content Associated with Users == | ||
| - | Unlike other database types, Oracle doesn't have a concept of 'individual databases.' Instead, it uses one database with different users/views. If you drop a user, all content associated with them will be removed (equivalent to MySQL's DROP DATABASE). To perform this: | ||
| - | <pre>DROP USER testuser CASCADE;</pre> | ||
| - | == Altering Passwords == | ||
| - | Run the following to change the password of a user: | ||
| - | <pre>ALTER USER testuser IDENTIFIED BY newpassword;</pre> | ||
| - | == Data Pumping Export & Import == | ||
| - | The original documentation can be found at [http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm#BCEIHIHE http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm#BCEIHIHE], but posting here for quick reference. '''NOTE THAT SQL DEVELOPER DOES NOT SUPPORT EXPORT OF CLOB/BLOB TYPES''' SO EXPORT THROUGH THE COMMAND LINE TO A BINARY FORMAT IS PREFERRED. | ||
| - | Run the following command to setup a dump directory (if not already set up & assuming directory exists): | ||
| - | <pre>CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp'; | ||
| - | GRANT READ,WRITE ON DIRECTORY dmpdir TO testuser;</pre> | ||
| - | Then the export can be performed from the command prompt: | ||
| - | <pre>expdp SYSTEM/password SCHEMAS=testuser DIRECTORY=dmpdir DUMPFILE=testuserschema.dmp LOGFILE=exptestuserschema.log</pre> | ||
| - | An import to a new user can be performed as following without having to create the new user first: | ||
| - | <pre>impdp SYSTEM/password SCHEMAS=testuser DIRECTORY=dmpdir DUMPFILE=testuserschema.dmp | ||
| - | REMAP_SCHEMA=testuser:testuserdev EXCLUDE=constraint, ref_constraint, index | ||
| - | TABLE_EXISTS_ACTION=replace LOGFILE=imptestuserschema.log</pre> | ||
