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.
This section lists common SQL tasks involving already existing data.
Run the following to change the password of a user:
ALTER USER testuser IDENTIFIED BY newpassword;
To increase the amount of space, add another available file to the tablespace:
ALTER TABLESPACE ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM1.DBF' size 1024M;
To increase the amount of space, locate the file from the previous section and run the following to increase:
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' AUTOEXTEND ON NEXT 32M MAXSIZE 1024M;
To increase the amount of space, locate the file from the previous section and run the following to increase:
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 2048M;
This section lists common SQL tasks that pertain to creating records or creating tables.
This will create a new tablespace for Oracle. An example would be for anthillpro imports that rely on multiple tablespaces, you could remap them to this one.
CREATE TABLESPACE import DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\IMPORT.DBF' size 2047M;
This will create a username with a password, and grant the roles required out of the XE documentation:
create user testuser identified by testpassword; grant connect, resource, create session, create table to testuser;
This section lists common SQL tasks that deal with deleting/dropping information out of the database.
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:
DROP USER testuser CASCADE;
This section will list SQL that is more-or-less used to find out information about the state of Oracle/database.
If an error occurs that the tablespaces are too large, the current size can be checked with the following SQL:
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;
This will show the currently existing users in all of Oracle:
select * from all_users;
Anything that wouldn't fit under the previous categories.
The original documentation can be found at 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):
CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp'; GRANT READ,WRITE ON DIRECTORY dmpdir TO testuser;
Then the export can be performed from the command prompt:
expdp SYSTEM/password SCHEMAS=testuser DIRECTORY=dmpdir DUMPFILE=testuserschema.dmp LOGFILE=exptestuserschema.log
An import to a new user can be performed as following without having to create the new user first:
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
If tablespaces need remapped, append the following:
REMAP_TABLESPACE=src1:dst1 REMAP_TABLESPACE=src2:dst2
Here is all I did for my install:
SQL-PLUS SETUP:
SQL> CREATE USER JDK7_387 IDENTIFIED BY JDK7_387; User created. SQL> GRANT CONNECT, RESOURCE TO JDK7_387; Grant succeeded. SQL>
ANTHILL INSTALL: (note that this install consisted of a full filesystem, no database, and a configured installed.properties file)
Buildfile: install.with.groovy.xml
install:
[echo]
[echo] Installing AnthillPro3 Server 3.8.7_303013
[echo] Enter the directory where the AnthillPro3 server should be installed.
%SERVER%
[echo]
[echo] Installing AnthillPro3 Server to: %SERVER%
[echo] Please enter the home directory of the JRE/JDK used to run the server. [Default: C:\java\170u05x64]
[echo] JVM Version detected: 1.7.0_05
[echo] JAVA_HOME: C:\java\170u05x64
[echo] Enter the database schema name. (required if user has DBA role)
[copy] Copying 3 files to %INSTALLER%\lib\ext
.
.
.
[copy] Copying 2 files to %SERVER%\bin\service
[echo] Do you want to install Anthill3 Server as Windows service? y,N [Default: N]
[echo]
[echo] You can install service manually (see documentation).
[echo]
[propertyfile] Updating property file: %SERVER%\conf\server\installed.properties
[echo] Creating Database ...
[sql] Executing resource: %INSTALLER%\database\oracle\anthill3.ddl
[sql] 153 of 153 SQL statements executed successfully
[echo] Creating Foreign Keys ...
[sql] Executing resource: %INSTALLER%\database\foreign-keys.ddl
[sql] 197 of 197 SQL statements executed successfully
[echo] Creating Indexes ...
[sql] Executing resource: %INSTALLER%\database\anthill3-indexes.ddl
[sql] 239 of 239 SQL statements executed successfully
[echo] Inserting data ...
[dbunit] Executing operation: INSERT
[dbunit] on file: %INSTALLER%\database\anthill3-data-insert.xml
[dbunit] with format: xml
[echo] Creating Permissions...
[propertyfile] Creating new property file: %SERVER%\conf\installed.version
[propertyfile] Updating property file: %SERVER%\conf\installed.version
[copy] Copying 247 files to %SERVER%\agent-upgrade
[copy] Copied 28 empty directories to 2 empty directories under %SERVER%\agent-upgrade
[copy] Copying 50 files to %SERVER%\agent-upgrade\lib
[mkdir] Created dir: %SERVER%\opt\tomcat\webapps\ROOT\tools\agent
[zip] Building zip: %SERVER%\opt\tomcat\webapps\ROOT\tools\agent\anthill3-agent-install.zip
[unzip] Expanding: %INSTALLER%\agent\conf.zip into %SERVER%\agent-upgrade
[copy] Copying 1 file to %SERVER%\opt\tomcat\webapps\ROOT\tools\agent
[zip] Updating zip: %SERVER%\opt\tomcat\webapps\ROOT\tools\agent\agent-upgrade.jar
[delete] Deleting directory %SERVER%\agent-upgrade
[echo] After starting the AnthillPro3 server, you may access the web UI by pointing your web-browser at
[echo] http://localhost:80/ to complete the Installation.
[echo] Installer Complete. (press return to exit installer)
