|
(Revised with subsections)
|
(Alphabetized)
|
| 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. | ||
| - | == Informational SQL == | ||
| - | This section will list SQL that is more-or-less used to find out information about the state of Oracle/database. | ||
| - | === Show Current Users === | ||
| - | This will show the currently existing users in all of Oracle: | ||
| - | <pre>select * from all_users;</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> | ||
| == Altering SQL == | == Altering SQL == | ||
| This section lists common SQL tasks involving already existing data. | This section lists common SQL tasks involving already existing data. | ||
| Line 24: | |||
| <pre>ALTER USER testuser IDENTIFIED BY newpassword;</pre> | <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: | ||
| - | <pre>ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 2048M;</pre> | ||
| === Add Datafile to Tablespace === | === Add Datafile to Tablespace === | ||
| Line 40: | |||
| MAXSIZE 1024M;</pre> | MAXSIZE 1024M;</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> | ||
| == Creation SQL == | == Creation SQL == | ||
| This section lists common SQL tasks that pertain to creating records or creating tables. | This section lists common SQL tasks that pertain to creating records or creating tables. | ||
| Line 57: | |||
| 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> | ||
| + | == Informational SQL == | ||
| + | This section will list SQL that is more-or-less used to find out information about the state of Oracle/database. | ||
| + | === 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> | ||
| + | === Show Current Users === | ||
| + | This will show the currently existing users in all of Oracle: | ||
| + | <pre>select * from all_users;</pre> | ||
