Introduction to Oracle APEX 5 – Free Video Tutorial Introduction to the APEX Home Page and Workspaces April 22, 2017+. Introduction to Oracle APEX 5 – Free Video Tutorial Hierarchy of APEX Constructs April 22, 2017+. Introduction to Oracle APEX 5 – Free Video Tutorial APEX Architecture April 22, 2017+. In order to interact with Oracle you should use the SQL.Plus Application. This application is available on both the windows and Unix platforms. On Windows go to the Start Menu, then select Start Programs Oracle - DBBOOKHOME Application Development SQL. Plus. Use your installation name in place of DBBOOKHOME.
Knowing the most common tasks an Oracle Database Administrator (DBA) encounters and what a DBA needs to monitor daily can help keep your database running and well-maintained. Master your common command-line executables to develop your DBA expertise.
Oracle 12c SQL Commands
Thousands of SQL commands exist in the Oracle database realm. Here are some that Database Administrators use daily. This list could differ for each Oracle database you use.
To Do This | Use This Command |
---|---|
Create a tablespace | CREATE TABLESPACE users DATAFILE ‘/u01/oracle/oradata/dev12c/users01.dbf’ size 100M autoextend on next 10M maxsize 4G; |
Set up a user | CREATE USER ldehoff IDENTIFIED BY ‘welcome1’ DEFAULT TABLESPACE users QUOTA 10m ON users PASSWORD EXPIRE; CREATE ROLE developer_role; GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW to developer_role; GRANT developer_role TO ldehoff; |
Create a table | CREATE TABLE clients ( client_id NUMBER(4) CONSTRAINT pk_client_id PRIMARY KEY, last_name VARCHAR2(128) NOT NULL, first_name VARCHAR2(64) NOT NULL, acct_number NUMBER(8) CONSTRAINT uk_acct_number UNIQUE, advisor_id NUMBER(4) CONSTRAINT fk_advisor_id REFERENCES advisors(advisor_id) ) TABLESPACE users; |
Create an index | CREATE INDEX idx_last_name ON clients(last_name) TABLESPACE indexes; |
Create a view | CREATE OR REPLACE VIEW client_vw AS SELECT last_name||’,’||first_name “LAST, FIRST”, client_id FROM clients; |
Create a sequence | CREATE SEQUENCE client_id_seq INCREMENT BY 10 START WITH 10; |
Use a sequence | INSERT INTO clients VALUES (client_id.nextval, ‘Schumacher’, ‘Michael’, 1000, 1234); |
Drop a table | DROP TABLE advisors CASCADE CONSTRAINTS; |
Flash back to a table | FLASHBACK TABLE advisors TO BEFORE DROP; |
Join two tables | SELECT last_name, acct_number, advisor_last_name FROM clients JOIN advisors USING (advisor_id); |
Start listener from command line | lsnrctl start |
Start Enterprise Manager Database Control from command line | emctl start dbconsole |
Daily Watchlist for Database Administrators
Database Administrators (DBAs) frequently check the health of the database in an organization. DBAs sometimes refer to these daily tasks as “keeping the lights on.” As a DBA, you’ll want to keep a close eye on the following areas:
- Space
- System logs
- Audit records
- Sessions
- Backups
- Batch jobs
- Enterprise Manager
Command-Line Executables for Oracle Tools
Oracle has several command-line utilities. Here are the most common ones. If you aren’t sure about how to use them, try typing in the command with help=y.
Tool | Command |
---|---|
SQL*Plus | sqlplus |
Listener Control | lsnrctl |
Data Pump Export | expdp |
Data Pump Import | impdp |
Recovery Manager | rman |
SQL Developer | sqldeveloper |
Net Manager | netmgr |
Network Configuration Assistant | netca |
Oracle Database Login Chart
You should never write down passwords to your real Oracle databases. However, while learning and filling your brain with knowledge, forgetting your passwords for different functionalities should be the least of your concerns.
Database Name Tool (e.g. SQL*Plus) | Username | Password |
---|---|---|
__________________________________ | ______________ | ______________ |
__________________________________ | ______________ | ______________ |
__________________________________ | ______________ | ______________ |
Don’t put any production information above!