|
(→Initial User Creation)
|
(Revised with subsections)
|
| Line 2: | |||
| 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. | 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 == | + | == Informational SQL == |
| + | This section will list SQL that is more-or-less used to find out information about the state of Oracle/database. | ||
| - | This will create a username with a password, and grant the roles required out of the XE documentation: | + | === Show Current Users === |
| - | <pre>create user testuser identified by testpassword; | + | This will show the currently existing users in all of Oracle: |
| - | grant connect, resource, create session, create table to testuser; | + | <pre>select * from all_users;</pre> |
| - | </pre> | ||
| - | == Get Current Tablespace Size == | + | === Get Current Tablespace Size === |
| If an error occurs that the tablespaces are too large, the current size can be checked with the following SQL: | If an error occurs that the tablespaces are too large, the current size can be checked with the following SQL: | ||
| Line 16: | |||
| a.file_id,b.file_name, autoextensible,b.bytes/1024/1024;</pre> | a.file_id,b.file_name, autoextensible,b.bytes/1024/1024;</pre> | ||
| - | == Increase Tablespace Size == | + | == Altering SQL == |
| + | This section lists common SQL tasks involving already existing data. | ||
| + | === Altering Passwords === | ||
| + | Run the following to change the password of a user: | ||
| + | <pre>ALTER USER testuser IDENTIFIED BY newpassword;</pre> | ||
| + | === Increase Tablespace Size === | ||
| To increase the amount of space, locate the file from the previous section and run the following to increase: | 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> | <pre>ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 2048M;</pre> | ||
| Line 23: | |||
| - | == Add Datafile to Tablespace == | + | === Add Datafile to Tablespace === |
| To increase the amount of space, add another available file to the tablespace: | To increase the amount of space, add another available file to the tablespace: | ||
| <pre>ALTER TABLESPACE ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM1.DBF' size 1024M;</pre> | <pre>ALTER TABLESPACE ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM1.DBF' size 1024M;</pre> | ||
| - | == AutoExtend Tablespace Sizes == | + | === AutoExtend Tablespace Sizes === |
| To increase the amount of space, locate the file from the previous section and run the following to increase: | 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' AUTOEXTEND ON NEXT 32M | <pre>ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 32M | ||
| MAXSIZE 1024M;</pre> | MAXSIZE 1024M;</pre> | ||
| - | == Drop all Tables/Content Associated with Users == | ||
| + | == Creation SQL == | ||
| + | This section lists common SQL tasks that pertain to creating records or creating tables. | ||
| + | === 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, create session, create table to testuser; | ||
| + | </pre> | ||
| + | == Deletion SQL == | ||
| + | This section lists common SQL tasks that deal with deleting/dropping information out of the database. | ||
| + | === 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: | 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> | <pre>DROP USER testuser CASCADE;</pre> | ||
| Line 38: | |||
| - | == Altering Passwords == | ||
| - | Run the following to change the password of a user: | + | == Miscellaneous SQL == |
| - | <pre>ALTER USER testuser IDENTIFIED BY newpassword;</pre> | + | Anything that wouldn't fit under the previous categories. |
| + | === Data Pumping Export & Import === | ||
| - | == 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. | 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. | ||
| Line 57: | |||
| TABLE_EXISTS_ACTION=replace LOGFILE=imptestuserschema.log</pre> | TABLE_EXISTS_ACTION=replace LOGFILE=imptestuserschema.log</pre> | ||
| + | <br/> | ||
| + | <br/> | ||
| + | <br/> | ||
| + | <br/> | ||
| = Test Install Run = | = Test Install Run = | ||
