Oracle 11gR2
----------------------------------------
----------------------------------------
----------------------------------------
--Quick Terminology
--------------------
--ORACLE_BASE: Home directory for the oracle software. Example: C:\oracle\gemi\product\11.2.0 **before it goes into the databases
--ORACLE_HOME: Files for each database. Example: C:\oracle\gemi\product\11.2.0\dbhome_1
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle Enterprise Manager Tips and More--
/*
-Host Credentials: These are the current credentials of the server admin that owns the database. For a few few actions, OEM will
first ask you to provide the host (host server/computer) credentials before making the change. Since these are not credentials
to the database, this will change when you update the admin password. An exmple of this is that the Gemi database can be
accessed through oracle set credentials such as the schema owner GEMI, a specified user whith minimum rights to
view/access the database, and of course system admins for SYS and SYSTEM; but host credentials are the admin
account of the server.
--Session Analysis
Within Oracle Enterprise Manager, you can view the top sessions, kill sessions, search sessions, and analyse session activity.
Within the database home console, select the Performance tab, and browse session activity from there. Sessions can often be
monitored easily with OEM due to all of the visual summaries of sessions and effective navigation. You can search top sessions,
sessions that are causing blocking, instance locks, which sessions are the top consumers, which sessions are being blocked, and
much more activity. I have used the session search in order to find sessions that were causing errors within the database and
effectivelly kill them.
--General Database Management
OEM provides general database management easily within the console. You can add objects such as tables, packages, schema items,
and many more easily. Performing maintenance such as setting up backups and schedules, performing exports and imports, and setting
up performance reporting.
--ORACLE SUMMARIES:
Oracle summaries are special kind of aggregate views that improve the query execution times by precalculating expensive
joins and agregation operations prior to execution. It stores the result within the databases itself. Materialized views
are similar.
--OLTP: Online Transaction Processing
OLTP systems capture, validate, and store large amounts of transactions. They are designed to respond immediately
to user requests; therefor are usually built with large amounts of reletively short database transactions that are optimized
for speed. Usually, data entry operations and quick comparison are what these are designed to do.
Examples: ATM, Order Entry System, etc.
This is all in contrast to the OLAP: which is online Analytical Processing.
--OLAP: Online Analytical processing
OLAP, in contrast to OLTP, is often referred to as larger, detailed data. OLAP's have far less constant transactions that the OLTP
with more complex query and schema design. There is more aggregations and comparisons taking place to fulfill the role that the database
plays for analysis.
/*
----------------------------------------
----------------------------------------
----------------------------------------
--Example of login and execute a script from folder location
---------------------------------------------------------------
--Within Command line, you can log into sqlplus and run a script directly from a sql file within
--any location.
--Command line for logging into a database and running a script from a file location
sqlplus user1/mypassword@GEMI @"\\myServer\myfolder\gemi_script.sql"
--other location examples
sqlplus user1/mypassword@GEMI @"\\myServer\myfolder\stop_transaction.sql";
sqlplus user1/mypassword@GEMI @"Y:\myStuff\search_sessions.sql";
----------------------------------------
----------------------------------------
----------------------------------------
--Auto Increment on Primary Keys
--------------------------------------
--Primarey keys are defined as such in sql server:
create table blarg (ID int IDENTITY (1,1) not null, inventory int null);
--Primarey keys and assigning an auto increment is quite different in oracle
create table blarg (ID number, inventory number not null);
ALTER table blarg
ADD (constraint pk_blarg_id) primary key (id));
create sequence seq_blarg_id minvalue 1 start with 1 increments by 1;
--Now that the sequence exists, a trigger must be applied to the table to use it.
--SPECIAL NOTE: In SQL Developer, this must be run to execute together.
--If not, the :new syntax will be questioned and expect a value to be entered rather
--than reading it together as apart of the trigger. This can be done using F9.
create or replace trigger blarg_id
BEFORE INSERT on blarg
FOR EACH ROW
BEGIN
select seq_blarg_id.nextval
into :new.ID
from dual;
end;
--Now that the trigger is built, it needs to be enabled.
Alter trigger blarg_id ENABLE;
--Now you should have a perfect auto increment. Much like SQL Server, you do not include the id within the select statement,
--because the increment will be handled by the trigger from now on.
--Note: a Identity keyword was added in orace 12 that functions much like SQL Server. However, it will never apply to 11g.
--dropping a sequence
drop sequence seq_gemitesttable1_id;
----------------------------------------
----------------------------------------
----------------------------------------
--VARIABLES--
-------------
--Variables are handled differently in oracle. SQL Server used the DECLARE and SET keywords in order to declare a variable and
--assign a value. The temp tables used were processed in the background.
--SQL Server also had the functonality to run logical code throughout, but Oracle seems to need variables to be set,
--defined, and displayed differently depending on the chosen method.
--Example of sql server code for comparison - set variable within Declare.
DECLARE @sillyVariable as varchar(30) = 'I have a cat named Dr. Zoidberg!';
PRINT @sillyVariable;
--Example of sql server code - use SET to set the variable
DECLARE @variable2 as varchar(30);
SET @variable2 = 'I have a cat named Dr. Zoidberg!';
PRINT @variable2;
--Exmaple in sql server code - count to 12!
--prints 0 to 12 in sql server
DECLARE @1 int =0
DECLARE @2 int =2
while @1 < @2
begin
print @1
set @1=@1+1
end;
-------------------
--DECLARE variable:--
--The declare variable cannot be run outside of the BEGIN and END statements. Calculation must be
--performed within the statements as well. If the DBMS_OUTPUT.PUT_LINE(silly3) was attempted again outside of
--the begin and end statements, an error will be produced.
--DECLARE variable and calculation
set serveroutput on;
DECLARE
silly3 NUMBER;
silly7 NUMBER;
BEGIN
silly3 := 6;
silly7 := 7+ silly3;
DBMS_OUTPUT.PUT_LINE(silly3);
DBMS_OUTPUT.PUT_LINE(silly7);
END;
---DECLARE variable with loops and calculation
--Counts to 18, but prints the text under it upon every iteration.
set serveroutput on;
declare
n1 number := 0;
n2 number := 18;
mine22 varchar2(50);
begin
while n1 < n2
loop
mine22 := 'the fox ran away';
dbms_output.put_line(n1);
dbms_output.put_line(mine22);
n1 := n1 + 1;
end loop;
end;
--DECLARE with select INTO, calculation, and condition.
--Unlike the above statement, this example uses 'select into' to define the variable rather than 'var333 :='
set serveroutput on;
DECLARE
silly3 NUMBER;
silly7 NUMBER;
silly5 NUMBER;
BEGIN
select 6 into silly3 from dual;
select (7 + silly3) into silly7 from dual;
select 9 into silly5 from dual;
DBMS_OUTPUT.PUT_LINE(silly3);
DBMS_OUTPUT.PUT_LINE(silly7);
DBMS_OUTPUT.PUT_LINE(silly5);
END;
--DECLARE within a loop:
--When coducting a loop in oracle, var and exec cannot be used. A loop such as this will expect declared variables.
--Output: 0-17
set serveroutput on;
declare
n1 number := 0;
n2 number := 18;
begin
while n1 < n2
loop
dbms_output.put_line(n1);
n1 := n1 + 1;
end loop;
end;
--You may notice that these statements all possess the 'set serveroutput on' command. This
--is needed in order to display the DBMS_OUTPUT.PUT_LINE statement.
--------------------
--DEFINE variable:--
--Unlike the declare statement, the define variable does not need to exclusively exist wtihin begin/end statements.
--Furthermore, it does not need to be defined as any particular data type. This variable can be called anywhere
--within the instance, and can be undifined later. However, undifining the variable is not neccessary if the instance
--is later closed.
--Define needs to be referenced with an &.
--Define needs to be referenced using apostrophe's if the variable is a text format. If it is an integer, then the apostrophes
--are not needed.
--Define will not work with the PRINT command.
--Define and use in a where clause
define petName = 'Bob';
select name from cat_table
where name = '&petName';
--Define and use to select from dual
define petName = 'Bob';
select '&petName' from dual;
--Define as an integer (does not need the apostrophes)
define myInt = 10;
select &myInt from dual;
--Use Undefine
UNDEFINE petName;
----------------
--VAR or VARIABLE:--
--Var and VARIABLE are interchangebly used for this variable setting. Like DEFINE, it does not have to exclusively
--exist within begin/end statements; but can be used within them. Var is instance specific. Even though you can
--run the print statement whenever you would like, it will not work once the instance is closed.
--Because var is stored in a system table, you can print the value of the variable anytime in the query. This is comparable to the
--define command that is unable to be printed.
--Simple var statement - populate using EXEC
var cat6 varchar2(30);
exec :cat6 := 'green cable';
print cat6;
--Example of a var in begin/end statements - populate using select into
var something33 number;
begin
select 33 into :something33 from dual; --setting it to 33
end;
PRINT something33;
--Example of multiple variables being stored:
--name (populate with select)
var name12 varchar2(30);
begin
select 'kitty-caterpillar' into :name12 from dual;
end;
--A number (populate with exec)
var something33 number;
exec :something33 := 33;
--after running the above, the values will be stored. Now you can query the print command in order to retrieve them.
print name12;
print something33;
\\**\\
--Example of multiple variables used in both print and dbms output statements (intended to be run in SQLPLUS, not SQL Developer):
--This code connects to different databases, and keeps count of the successful connections it makes.Originally was used to
--run scripts successfully within each database connection, and a count was kept as to which ones were complete.
var connected_count NUMBER; --Set variable for connection counts
var connected_txt varchar2(24); --set variable for connection count text
var connected_msg varchar2(50); --set variable for combined connection message (combines connect_count and connected_txt)
exec :connected_count := 0;
exec :connected_txt := 'Successful Connections: ';
--A connection that is set to increment the count variable if successful
connect GEMI/mypasswordSTuff@GEMI;
exec :connected_count := :connected_count +1; --adds plus 1
--set value for the connected message. This cannot be set above because this will capture the successful connection counts at the end.
exec :connected_msg := :connected_txt || :connected_count;
--print them in dbms
set serveroutput on;
exec dbms_output.put_line (:connected_msg); --Output "Successful Connections: 1";
--print them in dbms with additional text
set serveroutput on;
exec dbms_output.put_line ('The server can connect to these environments. ' || :connected_msg); --Output "The server can connect to these environments. Successful Connections: 1";
--print them in regular print
PRINT :connected_msg; --Output "Successful Connections: 1";
\\**\\
----------------------------------------
----------------------------------------
----------------------------------------
--OUTPUT Setting in SQLPLUS and PLSQL in SQL DEVELOPER
--Unlike in SQL Server, sqlplus and plsql require that the output setting is defined in order to display values such
--as variables and custom messages using he dbms_output.put_line command. For the text to be displayed correctly,
--the serveroutput needs to be set to on within the session.
--setting the serveroutput on
SET SERVEROUTPUT ON;
--This is purely session based. By default, using the dbms_output command will not display the value, but will instead simply
--confirm successful run. Once the serveroutput is set to we can now see the intended variable value or message. As with any display,
--it is not neccessary for the developer to see it for the variables to be used within the code, but being able to query the values being
--passed is optimal.
--PLSQL in SQL Developer functions a bit different than SQLPLUS. This works within stored procedures and functions.
--Serveroutput - without variables
SET SERVEROUTPUT ON;
BEGIN --The dbms output should be placed within a begin and end tag to function within SQL Developer for procedures and functions.
--Now we can see the message we want to generate.
dbms_output.put_line('The table already exists.');
END;
--Serveroutput - with variables
DECLARE silly3 varchar2(40);
BEGIN
silly3 := 'I like turtles.';
DBMS_OUTPUT.PUT_LINE(silly3);
END;
--However we can also make a quick output in sql developer without using the begin and end.
SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.PUT_LINE ('This is a message!!!!!!!');
--**Special Note: The output on/off along with using Begin/End is unique to PLSQL; which is used in SQL Developer, and within
--stored procedures/functions. It is very different in SQLPLUS from cmd.
--SQLPLUS--
--DBMS output functions slightly differently than in PLSQL. The difference is that it must be executed with the 'exec' at the
--beginning of the dbms_ouptut line because cmd will not use Begin/End statements surrounding it. If used, they stop the execution.
--As for the serveroutput setting, it can technically execute without needing for the output to be turned on; but the message will not be
--seen.
EXEC DBMS_OUTPUT.PUT_LINE ('This is a message!!!!!!!');
--**Output: procedures successfully completed.
SET SERVEROUTPUT ON;
EXEC DBMS_OUTPUT.PUT_LINE ('This is a message!!!!!!!');
--Using Variables and Text:
set serveroutput on;
exec dbms_output.put_line (:myVariable11 || ' is the message we are receiving.');
--print them in dbms with additional text. Let's say that myVariable11 is a combination variable between 'Successfull Connections: ' and
--a number field '1'.
set serveroutput on;
exec dbms_output.put_line ('The server can connect to these environments. ' || :myVariable11);
--Output "The server can connect to these environments. Successful Connections: 1";
PRINT--
--Print is another method used for output. Print is used the same way accross both SQLPLUS and PLSQL.
--One of the larger differences between print and dbms_output.put_line is that unbound text cannot be simply
--placed within PRINT. Another is that the serveroutput does not need to be tampered with.
PRINT :myVariable11;
PRINT myVariable11;
PRINT :myVariable11 || :myMessage || :myText;
----------------------------------------
----------------------------------------
----------------------------------------
----------------------------------------
----------------------------------------
----------------------------------------
--IF STATEMENTS
-----------------
--Oracles IF statement is not as smart as the code in sql server and needs variables defined in place of
--running queries directly within the comparison. SQL Server is able to use subqueries and other code such as
--EXISTS within the IF statement, but Oracle cannot. Oracle if statements must start with Declaring variables.
--In sql server, Exist can be used!
if exists(select col1 from table1)
then
else
end;
--In oracle, looking for that column in that table must be performed on the variable level first, then action is performed
--within the if/then/else statements.
--Look for specific table. If it exists, the message will say that it exists. If it does not, it will create the table.
--Declare the variables in the if statement
--wrapped into a proc
create or replace procedure gemi_createTable
as
begin
--declare
DECLARE
checkExists number;
--start the if statement
BEGIN
--This is checking for the tables existance by running a count on the sys table and inserting that value into the variable
select count(*) into checkExists from sys.all_tables where table_name like 'GEMI_TEMP_1';
IF (checkExists = 0)
THEN
--Show comment that the table will be created
dbms_output.put_line('The table did not exist. Creating table.');
--This will create the table
EXECUTE IMMEDIATE 'CREATE TABLE GEMI_TEMP_1(cat_col number(3) not null)';
ELSE
--Do nothing. All this shows is a message that the table exists.
dbms_output.put_line('The table already exists.');
END IF;
--Message that states that the process is complete
dbms_output.put_line('Complete!!!');
END;
--end the proc
END gemi_createTable;
--Another thing to note is that the if statement does not seem to call global variables well. If a variable
--is defined outside of the if statement, it has problems using it. Also unlike SQL server, code cannot be directly
--run within the statement. I had to use the Execute immediate in order to run the CREATE statement, and I had to
--substitute the dbms_output.put_line for custom statements.
--print will print a variable such as variable_cat_1 (variable example not included).
print variable_cat_1; --produces 'HI!'
select 'Blah!' from dual; --similar to sql servers code: (select 'Blah!')
exec dbms_output.put_line(variable_cat_1); --produces 'HI!' but it needs to be run within a begin/end using declare for the variable
----------------------------------------
----------------------------------------
----------------------------------------
--STORED PROCEDURES
----------------------
--Stored procedures do not need to have either alter or create, as the Oracle process
--features 'CREATE OR REPLACE' that will perform either as needed.
--The Main syntax
create or replace procedure my_proc_1
AS
BEGIN
--body
Select * from gemi_table1;
END my_proc_1;
--Here is an example of building a stored procedure using the if statement I had built above.
--Stored Procedure
CREATE OR REPLACE PROCEDURE gemi_procedure1
AS
BEGIN
--declare the variables in the if statement
DECLARE
checkExists number;
--start the if statement
BEGIN
--This is checking for the tables existance by running a count on the sys table and inserting that value into the variable
select count(*) into checkExists from sys.all_tables where table_name like 'GEMI_TEMP_1';
IF (checkExists = 0)
THEN
--Show comment that the table will be created
dbms_output.put_line('The table did not exist. Creating table.');
--This will create the table
EXECUTE IMMEDIATE 'CREATE TABLE GEMI_TEMP_1(cat_col number(3) not null)';
ELSE
--Do nothing. All this shows is a message that the table exists.
dbms_output.put_line('The table already exists.');
END IF;
--Message that states that the process is complete
dbms_output.put_line('Complete!!!');
END;
--end proc
END gemi_procedure1;
----------------------------------------
----------------------------------------
----------------------------------------
--Trigger Date Default--
create or replace trigger cat_office_visit_update
BEFORE INSERT or update on cat_office_visit
FOR EACH ROW
BEGIN
:new.updated_at :=sysdate;
end;
----------------------------------------
----------------------------------------
----------------------------------------
--ALTER SESSION / USE DB / Connection--
--SQL Server - change to database on network
--The USE script is used for switching to existing databases on the network. This can be performed through referencing the
--database name, or even doing so by referencing the IP.
USE gemi_db;
--Oracle SQL Developer -- switch 'schema' in database
--This is used to switch which schema you want to operate in. Unlike SQL Server, Oracle is often used to install
--only one database and use multiple 'schemas'. In most cases, SQL Server installs an instance, and you place multiple
--databases on that server instance. **used in both oracle cmd and Developer
alter session set current_schema = gemi;
alter session set current_schema = hr;
--Oracle CMD - Change database connection on server
--This can be performed on command line, within a stored procedure, or even within a sql script run by a batch file.
connect GEMI/mypassword33; --db on my computer connected to from tns
connect GEMI/password33@GEMI --db on server connected to from tns (The DB is also called GEMI).
--sqlplus connect
sqlplus connect GEMI/mypass88 --as regular
sqlplus connect SYS/mypass99 as sysdba --as sysdba
sqlplus connect SYSTEM/mypass99 as sysdba --as sysdba on system
sqlplus connect HR/mypass99@GEMI as sysdba --as sysdba to TNS.
----------------------------------------
----------------------------------------
----------------------------------------
--KILL SESSIONS IN ORACLE--
--Sessions are open by users within applications, internal connections, and external connections. You can not only query the sessions,
--but you can kill them as well. Note: Killing the session does not prevent the user from re-connecting immediately. Also, may
--not work when sessions are being opened by connections pools.
select * from gv$ession;
select * from V$ession;
SELECT *
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
--add a where clause to look for specific machines
where machine like '%PANDORA-PC%';
--When you find the session you are looking for, take not eof the SID and the the serial number. format them in 'sid,serial'.
alter system kill session '68,32683' immediate;
--Syntax
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
----------------------------------------
----------------------------------------
----------------------------------------
--SPOOLING
--Sql uses spooling to generate an ouput from a query and store them within a named file.
--SPOOLING CMD - simple
------------------
--log into CMD
sqlplus GEMI/passwords33 as sysdba;
--Sets the spool to go to the file
spool C:\spool.txt
--connect
CONNECT GEMI/myPasswords223@GEMI
--points to query file that the spooling will come from
@C:\query_for_spooling.sql
--set spool off
spool off
--The query_for_spooling.sql can feature anything from selecing * from table1 to even more complex queries.
--SPOOLING CMD - execute from file
------------------
--log into CMD
sqlplus GEMI/passwords33@GEMI as sysdba;
--execute the sql file that contains the spooling code
@C:\start.sql
--SPOOLING in sql Developer - with turning header off, linesize 100 etc.
------------------
set hea off
set linesize 100
set feedback off
set pagesize 0
spool C:\Users\gemi\Desktop\spool.txt
@C:\Users\gemi\Desktop\try.sql
--SPOOLING in sql Developer - just a query
------------------
set hea off
set linesize 100
set feedback off
set pagesize 0
spool C:\Users\gemi\Desktop\spool.txt
select * from gemitesttable33;
spool off
--Spooling From Multiple Queries
---------------------------------
--Spooling from multiple queries into one file.
set hea off
set linesize 100
set feedback off
set pagesize 0
--specify one spool
spool c:\ceoSequence.txt
connect gemi/mypassword@GEMI
select building_name from organization;
select name from ceo_table;
connect gemi/mypassword@GEMI_DB2
select building_name from organization;
select name from ceo_table;
connect gemi/mypassword@GEMI_DB9
select building_name from organization;
select name from ceo_table;
connect gemi/mypassword@GEMIDB
select building_name from organization;
select name from ceo_table;
connect gemi/mypassword@GEMI_IOWA
select building_name from organization;
select name from ceo_table;
--specify one spool off
spool off
----------------------------------------
----------------------------------------
----------------------------------------
--SQL PLUS FROM COMMAND LINE!!
--------------------------------
--Oracle connection sqlplus - server ID and Port
---------------
sqlplus GEMI/mypassword@//PANDORA-PC:1521/GEMI
sqlplus GEMI/mypassword@//PANDORA-PC:1521/GEMI as sysdba
--Oracle connection sqlplus - use IP address and port
---------------
sqlplus GEMI/mypass@1.2.3.44.55:1521/GEMI
sqlplus GEMI/mypass@MYTNSNAME as sysdba
sqlplus GEMI/mypass@1.2.3.44.55:1521/gemi.mydomain.com --domain
--Oracle connection sqlplus - Use TNS names
---------------
sqlplus GEMI/mypass@MYTNSNAME --(sqlplus GEMI/mypass@GEMIDB)
sqlplus GEMI/mypass@MYTNSNAME as sysdba
--Oracle connection sqlplus - Broken into [login to server] and then [connect to db]
---------------
sqlplus GEMI/mypass as sysdba
CONNECT GEMI/mypass;
select * from myTable;
-H Displays the sql plus version and the usage help.
-V Displays the SQL plus version.
-c Sets the compatibility of affected commands to the version specified by the version. The version has the form of
"x.y[.z]".
-M Sets automatic HTML markup of output. HTML ON|OFF, head text, body text, table text, entmap ON|OFF, preformat on/OFF.
-R Sets restricted mode to disable SQL Plus commands that interact with the file system. The level caqn be 1, 2, or 3. The
most restrictive s -R 3 which disables all user commands interacting with the file system.
-S Sets silent mode which supresses the display of the sqlplus banner, prompts, and echoing of commands.
logon: username/password@connect_identifier / as sysdba(or sysoper, sysasm);
username/passowrd@netservice_name//host:port/service_name;
**Net identifier can be in the form of the net service name or easy connect through TNS.
**Host specifies the host name or IP address of the server computer that the database resides on.
**Port specifies the listening port on the database server.
**Service Name specifies the service name of the database you want to access.
**The /nolog option starts splplus without connecting to the database.
**The Edition specifies the value for hte session edition.
Start is: @url|filename. It runs the specified sqlplus script from a web server url or the local file system filename.exe. with
specified parameters that will be assigned to substitution variables in the script.
When sqlplus starts, and after connect commands, the site profile and hte user profile are run.
----------------------------------------
----------------------------------------
----------------------------------------
--CAST AND CONVERT (CAST and TO_CHAR)
--------------------------------
--Cast is the same between sql server and sqlplus. The only difference are the data types; and of course the use of the dual table
--due to SQL plus not allowing code such as 'select 'James' as FIRST_NAME', or 'select getdate() as Todays_Date' that would be taken
--care of by background tables in sql server.
select cast(id as varchar2(20)) from gemitesttable1;
select cast(sysdate as varchar2(20)) from dual;
--Convert is quite different in sqlplus. Rather than using 'select CONVERT(VARCHAR(19),GETDATE()) ', oracle uses to_char and to_date.
select to_char(sysdate, 'MM/DD/YYYY') from dual;
select to_date('Jan 1, 2000', 'mm/dd/yy') from dual;
----------------------------------------
----------------------------------------
----------------------------------------
--SUBSTRING
--------------------------------
--Oracle sqlplus and sql server code is similar for substring; but only by spelling. SQL Server uses the statement SUBSTRING, where
--oracle uses the statement SUBSTR.
--SQLServer: select substring(name, 1, 3) from table33;
select substr(name, 1, 3) from table33;
----------------------------------------
----------------------------------------
----------------------------------------
--ROWNUM
------------------
--SQL Server uses ROWNUMBER OVER syntax, while oracle uses ROWNUM within the where clause.
select * from table32 where ROWNUM > 1;
**IN place of TOP
--RowNum is also used in place of the top function. Oracle SQL does not have the top funciton sql server has
--(select top 10 * from table33), so the rownumber has to be used in order to get the functionality.
--Rownum must be used with the less than or equals to number in order to select correctly.
select * from table 33 where ROWNUM <= 6;
----------------------------------------
----------------------------------------
----------------------------------------
--RPAD and LPAD
------------------
select rpad("name", 5)as SCREEEEE from gemitesttable1;
select lpad("name", 5)as SCREEEEE from gemitesttable1;
--Example
select sequence||' '||rpad(last_name||' '||first_name||' '||middle_name||' '||suffix,35)
from people_names
order by last_name;
----------------------------------------
----------------------------------------
----------------------------------------
--RMAN
----------
--Rman stands for the recovery manager for oracle. SQL Servers is taken care of in SQL Management Studio it'self, and some companies
--use the server tools to keep backups of a db.
--cannot connect to rman without being a sys login. This is default for rman, and will not be accessible otherwize.
--RMAN uses target database control files instead of recovery catelog.
--RMAN will automatically generate a 5 digit code for the process.
--Database will have to be set into archive mode in order to make a backup.
--Place db into archive mode. Check archive destination with use_db_recovery_file_dest. Default: Oracle_Home\fast_recovery_area;
show parameter recovery_file_dest;
--Show current database log mode, destination folder, status, and more. (No Archive Mode, )
archive log list
--If you ever want to change the archive log from the default recovery area, set them to a different location.
alter system set log_archive_dest_1='C:\ora\gemi\oradata\Gemi\new_recovery_October' scope = 1
--Show current database log most and
archive log list
--shutdown the db
shutdown immediate
--startup in mount mode
startup mount
--Set archive mode
alter database archivelog;
--open database
alter database open;
--now look at the archive log list. The Database log mode would be active, and the automatic archival is enabled.
archive log list
--
archive log list
--
Backup
set oracle_sid=db
echo %oracle_sid%
rman target / --represents current
--when connected, it will show the db name you are connecting to
backup database;
--finished backup
quit
--list db files. This will show them from the control file.
list backup
--delete backup
delete backup
backup database plus archivelog; --this is backing up the db plus the archive logs for the db. THis backs up the archive log then the files.
Recovery:
rman target /
startup nonmount; --this is performed because the instance needs to be started in preparation for a db to be restored. Nomount is due to the db not being there.
restore controlfile from 'filename'; --restore files
quit
--now that it is restored, it needs to be mounted.
alter database mount;
restore database --restore the db
recover database --
alter database open resetlogs;
server manager recovery
--RMAN is easier becuase it already knows the tablespace and structural knowlege of the db.
--DB needs to be in archive log mode in order to back it up when it is up and running.
shutdown immediate
delete*
--Check if db name is normal
select name from v$database;
--check open mode
select open_mode from V$database;
/*
**SPECIAL NOTE:
The archivelog needs ample space in order to function properly. Unfortunately, if it grows beyond it's determined space, it can
actually cause errors that will stop the database entirely. It is typically assumed that an issue like this will simply affect
the backup system, but it can ultimately crash your database.
Database Will Not Start – Will Not Open – ORA-03113 End of File on Communication Channel At Startup—
I was unable to log onto the database from either sqlplus or SQL Developer using GEMI or other schema logins.
-Oracle Service and Listener Service was running. Restarted them. No difference.
-Logged in sql plus as SYS with sysdba access. I was able to connect.
-startup: failed and produced ORA-03113 error.
-alter databse open; will not open.
-startup mount: I was able to mount the database, but was not able to startup or alter database open.
-Checked alert_gemi.log located in C:\app\thomasm\diag\rdbms\gemi\gemi\trace, and it was giving ORA-19815 starting that the
db_recovery_file_dest_size 0 remaining bytes available.
-Logged back into SYS as sysdba.
-Startup Mount: mount database
-alter database noarchivelog: turned archive log off because it was the problem.
-alter database open; database opened without errors.
- I was able to connect after this.
*/
----------------------------------------
----------------------------------------
----------------------------------------
--Database Replication Protection Level
----------------------------------------
--Maximum Protection:--
--LGWR SYNC = Max Availability & Max Protection
--This protection level uses a synchronous replication process to ensure that no data loss will occur if the primary database fails.
--It also enforces rules that prevent multiple failure events from causing data loss. This protection level will never allow a
--primary database to acknowledge commit success for an unprotected transaction.To provide this level of protection, the redo data
--that is needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least
--one standby database before Oracle can acknowledge commit success to the application. To ensure that data loss cannot occur, the
--primary database will shut down if a fault prevents it from writing its redo stream to the standby redo log of at least one standby
--database.
--Perform select to view current protection level
select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
--Set the database to maximum protection
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
--start recovery process on standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--Maximum Performance:
--LGWR ASYNC = Max Performance
--This protection mode (the default) is an asynchronous replication process that provides the highest level of data protection that
--is possible without affecting the performance of the primary database. This is accomplished by acknowledging commit success as
--soon as the redo data that is needed to recover that transaction is written to the local online redo log without waiting for
--confirmation by the standby that the data is protected. The redo data stream of the primary database is transmitted to the standby
--database directly from the Oracle in-memory log buffer as quickly as it is generated.
--Set log_archive
alter system set log_archive_dest_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY
--view current protection level
select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
--Maximum protection is the default
--Maximum Availability:
--This protection level uses a synchronous replication process that provides zero data loss protection without
--compromising the availability of the primary database. Like Maximum Protection, commit success is not acknowledged
--to the application until the redo that is needed to recover that transaction is written to the local online redo log and
--to the standby redo log of at least one standby database. Unlike Maximum Protection, however, the primary database does not
--shut down if a fault prevents it from writing its redo stream to a remote standby redo log. The primary database will stall
--for a maximum of net_timout seconds (user configurable) before proceeding, in order to maintain availability of the primary
--database. Data Guard automatically resynchronizes primary and standby databases when the connection is restored. Data loss
--is possible if a second failure occurs before the resynchronization process is complete.
--set standby type
alter system set log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)
--Set database
alter database set standby database to maximize availability;
--start recovery process
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--view current protection level
select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;
----------------------------------------
----------------------------------------
----------------------------------------
--RUNNING A JOB MANUALLY--
-----------------------------
EXEC dbms_scheduler.run_job('myLittleJob_name');
----------------------------------------
----------------------------------------
----------------------------------------
--CREATE TABLE FROM CURRENT TABLE--
-------------------------------------
--SQL Server has the following syntax for creating a copy of a table
select *
INTO myNewTable
from myExistingTable;
--Oracle, however, cannot use such syntax. It's syntax for creating a table
--from another is this:
create table tempTestIntoTable
as
select * from gemitesttable1;
Shown in Execute Immediate:
EXECUTE IMMEDIATE 'create table temp_copy_gemitesttable1 as (select * from gemitesttable1)';
----------------------------------------
----------------------------------------
----------------------------------------
--ALTER SESSION--
-------------------
alter session set current_schema = GEMI;
----------------------------------------
----------------------------------------
----------------------------------------
--Create Job In Scheduler--
----------------------------
--Look at all jobs
select * from sys.all_scheduler_jobs;
--Create job
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"GEMI"."MYJOB55"',
job_type => 'STORED_PROCEDURE',
job_action => 'GEMI.GEMI_CREATETABLE',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => NULL,
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'This job runs a stored procedure.');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"GEMI"."MYJOB55"',
attribute => 'restartable', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"GEMI"."MYJOB55"',
attribute => 'job_priority', value => '1');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"GEMI"."MYJOB55"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
DBMS_SCHEDULER.enable(
name => '"GEMI"."MYJOB55"');
END;
----------------------------------------
----------------------------------------
----------------------------------------
--SYS TABLES--
----------------
--Some of the most useful sys tables.
/*
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names
*/
----------------------------------------
----------------------------------------
----------------------------------------
--Materialized Views--
------------------------
--Select from materialized view:
select * from sys.all_snapshots where OWNER = 'GEMI' and NAME ='CAT_TABLE';
select * from sys.all_mviews;
select * from sys.dba_mviews;
select * from all_objects where OBJECT_TYPE='MATERIALIZED VIEW' order by owner;
--Drop Materialized View Log
DROP MATERIALIZED VIEW LOG ON GEMI.TABLE33;
--Create Materialized view logs
CREATE MATERIALIZED VIEW LOG ON GEMI.TABLE33
TABLESPACE USERS
WITH ROWID
INCLUDING NEW VALUES;
--Drop Materialized views
DROP MATERIALIZED VIEW GEMI.TABLE33;
--Remove Refresh Group
--DBMS_REFRESH enables you to create groups of materialized views that can be refreshed together to a transactionally
--consistent point in time.
--Create Materialized View
CREATE MATERIALIZED VIEW GEMI.TABLE44
TABLESPACE USERS
NOLOGGING
BUILD IMMEDIATE
REFRESH FAST
WITH ROWID
AS
select * from GEMI.MY_TABLE44;
--DBMS_REFRESH
--The DBMS_REFRESH package enables you to create groups of materialized views that can be refreshed together. The package
--executes to add the materialized view, change it, Destroy it, make it, refresh it, and subtract it.
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle Synonyms
--Synonyms are aliases for database objects, and can be used to hide ownership and location of the objects;
--while also making it easier to find the object if given a public type. The types are from public to private.
--Create
CREATE PUBLIC SYNONYM my_synonum_empNumbers33 for GEMI.empPhone;
--Drop
drop public synonym my_synonum33;
----------------------------------------
----------------------------------------
----------------------------------------
--Trigger Example:
--------------------
create or replace TRIGGER "GEMI"."TRIG"
before update or insert
on gym
for each row
-------------------------------------------------------------
-- Developed by GEMI 4/27/2020
--
--
-- Notes: Trigger will issue appropriate grants for every
-- object registered.
-------------------------------------------------------------
declare
pragma AUTONOMOUS_TRANSACTION;
cursor get_object is
select object_name
from site_objects;
begin
if (:NEW.registered_on is not null) Then
Execute Immediate ('GRANT SELECT ON GEMI TO '||:NEW.gym_name);
Execute Immediate ('GRANT INSERT ON LOAD_ERRORS TO '||:NEW.gym_name);
for objrec in get_object loop
dbms_output.put_line('Granting '||:NEW.gym_name||' permission on '||objrec.object_name);
Execute Immediate 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||objrec.object_name||' to '||:NEW.gym_name;
end loop;
end if;
**
--Triggers can work with ddl statements as well.
--Another Trigger:
**
create or replace trigger mytrig
before insert or update of vendor state
on vendors
for each row
when (new.vendor_state != upper(new.vendor_state))
begin
:new.vendor_state:=upper(:new.vendor_state);
end;
----------------------------------------
----------------------------------------
----------------------------------------
--DB Time, Date, and Timezone--
---------------------------------
--Oracle can be queried for time, date, and timezone. Even though most of these rely on the server date/time, there
--are a few things such as timezone settings that are stored within Oracle.
select systimestamp from dual; --13-SEP-16 11.23.43.416000000 AM -05:00
select current_timestamp from dual;
select dbtimezone from dual; --13-SEP-16 11.24.04.771000000 AM AMERICA/CHICAGO
select sysdate from dual; --13-SEP-16
select current_date from dual; --13-SEP-16
select timezone_hour from dual; --13-SEP-16 11.24.41.162000000 AM
select localtimestamp from dual; --13-SEP-16 11.25.10.247000000 AM
select sessiontimezone from dual; --America/Chicago
select * from v$timezone_names; --America/Chicago
select * from v$timezone_names where tzabbrev = 'CST';
--When looking to change the timezone, an alter can do so. However, it must be noted that the database has
--to be bounced for this to take effect. Even though it is advised to restart the server after such a change,
--the db should be bounced at minimul.
--Change the timezone
ALTER DATABASE <my db name> SET TIME_ZONE = 'US/Eastern'
or
ALTER DATABASE <my db name> SET TIME_ZONE = 'EST'
--view the changes
select dbtimezone from dual;
Bounce the db in sqlplus:
connect / as sysdba;
shutdown
startup
select dbtimezone from dual;
--Search by date
--Dates are handled in the DD-MMM-YY format, and need to be searched with 'like' and a percent at the end in order
--for the date to be searchable. You will receive no results without the like and percent.
select tree, date_planted from trees
where date_planted like '27-JUL-18%';
----------------------------------------
----------------------------------------
----------------------------------------
--Spooling and retrieving--
--How it works: The get_cat_office.sql file contains the file to spool to, the cat office connection credentials,
--the selections, and the script which retrieves the data.
set hea off
set linesize 100
set feedback off
set pagesize 0
spool c:\bro.txt
connect gemi/hahaPassword@GEMI
select ha from table44;
@D:\get_cat_office.sql
spool off
----------------------------------------
----------------------------------------
----------------------------------------
--Move Files--
--In oracle, we can move a file from one location to another using the host move sql command. This
--looks for the file listed, and moves it to the folder specified at the end. In this case, it will move a text
--file to another location. If the text file is not there (or if this command was run earlier) than Oracle will
--inform you that it cannot find the file specified.
--
host move "C:\Users\gemi\Desktop\cat.txt" "C:\Users\gemi\Documents";
--In a bat file, this code will be used. Usually, it is used when running multiple sql files and moves other
--files in between the time. But it is useful to utilize the sql code when needing to perform a move in a stored
--procedure or other methods that require the files to be moved in sql.
move /y d:\myexport\gemi*.dmp "\\GEMI-DB\DB_BACKUPS\mytest\"
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle Enterprise Manager - Export to Export Files--
--Location: Oracle Enterprise Manager > Data Movement > Export to Export Files
--To export files means that you can export database data documents to a binary file. It is stored this way
--outside of the database and can be read and easily imported by another oracle database.
--Exporting files in this way is simply easier and faster for Oracle to import data from another database. As
--with any export, you can export the data to flat files and import them into another database easily, but this option exists.
--Unlike how it sounds, this does not export to excel, csv, or other flat files. Exporting data to excel, csv,
--or other flat file format must be done in sql spooling, export wizard, UTL_FILE, or outside source such as ssis.
----------------------------------------
----------------------------------------
----------------------------------------
--Exporting And Importing Oracle Data to Flat File Delimited CSV--
--Query:
select to_char(ID) ||',' ||
to_char(NAME) ||', '||
to_char(SEX) ||', '||
to_char(BREED) ||', '||
to_char(AGE)||', '||
to_char(OWNER_PHONE) ||', '||
to_char(UPDATED_AT)
as cat_info
from cat_table;
----------------------------------------
----------------------------------------
----------------------------------------
--Code Advice--
/*
-Are things abstracted right?
-Did I pick meaningful, identifying names for variables and objects?
-Is there alog of code duplication?
-Are there ways I can simplify processes?
-Code needs to work, be easily read, and be well factored.
-When working on an existing project with a team, it is always a good idea to keep the syle of
the code consistent as possible, even though you have your own style of coding. This contributes to
the overall readability of your code, which is important.
-Each coder has a bit of a different style and it will show in most of your work. It is neither
terribly good or bad as long as the code is optimized to function at the best performance.
*/
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle Timezones and Intravals--
--Time zone Names:
select * from v$timezone_names;
--Deafault Session Time Zone:
select sessiontimezone from dual;
--default database timezone:
select dbtimezone from dual;
----------------------------------------
----------------------------------------
----------------------------------------
--Working with Large Ojbects--
--clob and nclob types store commonly large txt files and xml files.
--Blob objects commonly store data such as pdf files, images, sound files, and even video files.
--Bfile types store a pointer within files within the server. It can be told to point to anything
--outside of the db; including file folders that are storing pdfs or any other type of outside data.
--Some ap's for wokring with large objects:
--java, net_framework, c++, cobalt, pl/sql
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle For Loop--
--
for i in 3 loop
dbms.output.put_line('yes');
i:= i+1;
end loop;
----------------------------------------
----------------------------------------
----------------------------------------
--DB Hot Backup Through CMD--
/*
Usually, it is recommended that backups be performed using RMAN, but it is not a requirement. RMAN is an excellent tool that has
benefits in backup and restoration of the entire database while also being able to retrieve recent transactions,
but sometimes using other methods can be preferable.
Performing a backup through Windows Tasks Scheduler is using the CMD method. It's considered a hot method because
it requires simply to run the file in order to proceed and perform a backup.
One of the issues that RMAN presents is that the archivelogmode must have constant space to grow, mostly due to the
issues it causes if it finds that it lacks the space. Rather than RMAN generating an error and informing administrators,
it will actually crash the database and render it unuseable until archivelogmode is turned off. RMAN needs to be handled
with care and should only be used if intending to perform backups in accordance with it and intending to maintain it's
space. Many systems will take a system snapshot and purge the space over time to make sure that it has always has new
space; or at least implement other methods in order to maintain RMAN. In comparison, the Windows Task Manager method is
not only easily managed, but easy to perform backup and restores from. These decisions are purely based on preference of
the administrators and server managers.
*/
--The CMD Code:
--Place this cmd code into a file and save it to a .cmd file.
--This is an example of a export command. This will create a backup of the Gemi database and place it within the EXPORT directory.
--The directory is specified within the dba_directories table.
expdp GEMI/passpass@GEMI directory=EXPORT dumpfile=gemi.dmp full=y logfile=gemi.log reuse_dumpfiles=y
--In order to use this code, the 'directory' must be set up within Oracle. the directory command within the CMD code will
--not be able to use a path, but
--will be pointing to a valid Oracle directory, which is found in the dba_directories table. If this is not performed, you
--will receive errors
--stating that the directory is invalid.
select * from dba_directories;
--Create a directory and set the location on the server to a space you want.
CREATE DIRECTORY export AS 'C:\app\thomasm\admin\Gemi\dump';
--Now that the directory exists, grant read and write access to it. Oracle will let you know if this is not needed.
GRANT read, write ON DIRECTORY export TO GEMI;
Now that this is set up, the
.cmd file can be run.
--Setting up Windows Task Scheduler to run the CMD--
--Now that the cmd file is set up and ready to work, it can be called through Windows task scheduler. Create a new task. Set up a name,
--specify
--security options, enable the trigger to run at specified days and times, set up the actions to run the locaton\myBackupCMD.cmd and
--set up conditions. At the specified times, windows task scheduler will run the cmd file and create backups.
--VERSIONS OF EXPORT--
--Oracle 11g and earlier versions of oracle had two different methods for exporting using the cmd method; but are not able to be
--interchangably used.
--Older oracle versions used the -exp- method. This method held some different syntax, but upgraded systems will be not be able to use it.
exp userid=backup/backupPASS@server433 file=c:\export\server433.dmp log=c:\export\server433.log full=y consistent=y
--11g version
expdp backup/bacupPASS@GEMI directory=export dumpfile=otdc.dmp logfile=otdc.log full=y reuse_dumpfiles=y
----------------------------------------
----------------------------------------
----------------------------------------
--Restoring Oracle Database From Hot Backup--
-Copy tablespace files and the controlfilebackup form the backup location to the installation directory of the database instance.
-Rename it to CONTROL01.CTL as it was.
-Do not copy or restore the redo logs. In fact, delete the existing.
-Start the db in mount mode.
--SQL Code for Recovery:
Recover database until cancel using backup controlfile;
-The transaction logs have been applied. Run the following query to open the database for transactions:
alter database open resetlogs;
----------------------------------------
----------------------------------------
----------------------------------------
--Alter table Alter Column--
--Sql server alteres the column, but the modify term is used in oracle.
--alter a column
alter table cat_table
modify updated_at timestamp default sysdate;
----------------------------------------
----------------------------------------
----------------------------------------
--Add Foreign Key--
alter table cat_office_visit
add constraint fk_cat_office_visit_ID
foreign key (ID)
references cat_table(ID);
----------------------------------------
----------------------------------------
----------------------------------------
--Text || Variable (text+variable)
----------------------------------------
--There are quite a few times where we will need to produce special messages in SQL that mix variables and text.
--In many cases, sqlplus users usually choose the dbms_output.Put_line option, but this is not as effective when running
--scripts from the command line.
--A way of displaying messages without using dbms is to use the print. Print will only function with variables, and will not handle
--just splicing text around variables. One way I had made this work was to place the message text into a variable, and use both
--variables within PRINT.
---Set variable for connection counts
var connected_count NUMBER;
--set variable for connection count text
var connected_txt varchar2(24);
--set variable for combined connection message
var connected_msg varchar2(50);
--Set default value for connection counts to 0
exec :connected_count := 0;
--set value for connection count text
exec :connected_txt := 'Successful Connections: ';
--set value for connected message
exec :connected_msg := :connected_txt || :connected_count;
--Print count of successfull connections from variable that combines them
PRINT connected_msg;
--Print connected text and connected count seperately. This will place them on seperate lines, but it can be done this way.
PRINT connected_txt connected_count;
----------------------------------------
----------------------------------------
----------------------------------------
--Oracle SQL Search by Date--
select * from table22 where vet_date = '1-JAN-2011';
select date33 from table22 where date1 between '1-JUN-10' and '30-JUL-2012';
----------------------------------------
----------------------------------------
----------------------------------------
--ORACLE DB EXPORT VIA CMD
--Currently, we use a cmd file that is launched by windows task manager to perform daily backups of the database. Some of
--them are set up to keep multiple backup files set by the days of the week and saved over on that day a week later, while
--others are simply set to store one export file that runs daily.
--Example of export for every day of the week:
expdp backup/backupPASS@GEMI directory=export dumpfile=GEMI_-%date:~-4,4%%date:~-10,2%%date:~-7,2%.dmp full=y logfile=GEMI_-%date:~-4,4%%date:~-10,2%%date:~-7,2%.log reuse_dumpfiles=y
--Exmaple of simple single export:
expdp backup/backupPASS@GEMI directory=export dumpfile=gemi.dmp logfile=gemi.log full=y reuse_dumpfiles=y
----------------------------------------
----------------------------------------
----------------------------------------
--CONNECTING TO OTHER EXTERNAL DATABASES AND RUNNING SCRIPTS--
--There are several ways to connect to networked databases.
'connect ....
--When using the connect statement, you are connecting directly to the other database. You are no longer connected to the
--previous, and will have to use another connect in order to do so.
'select * from myTable22@GEMI.mywebconnection.org
--When using it within a sql statement, it applies to that statement only and does not log off of the current databases.
--You are connecting from the current connection. For this reason, oracle will only allow a few connections at a time. You
--should either use a commit, rollback, or entire connection change in order to run multiple sql statements to multiple databases
--while on one connection.
----------------------------------------
----------------------------------------
----------------------------------------
--Search Within Oracle SQL DEVELOPER CTRL F In Search Box
--SQL Develpers search is usually pretty forward except for when including expression characters and new lines within your specified search.
--Regular search: type it out as it is shown in your sql worksheet.
--the code in worksheet
test11 where t5 = 'beta 9098';
--search
test11 where t5 = 'beta 9098';
--Search Containing an expression: when searching for text that contain an expression, you will have to
--place brackets [] over the expression in order to be properly searched within Oracle SQL Developer.
--the code in worksheet
exec :connected_99 := :myCount +1
--search
exec :connected_99 := :myCount [+]1
--Search Containing a carriage return: when searching for text that contain carriage returns,
--you will have to specify it as \n. \n does not need to be bracketed or even have a trailing space.
--the code in worksheet
select c1 from table44
where c1 = 'beta 9097';
--search
select c1 from table 44 \nwhere c1 = 'beta 9097';
----------------------------------------
----------------------------------------
----------------------------------------
--Search\REPLACE Within Oracle SQL DEVELOPER CTRL F In Search Box
--SQL Develpers search is usually pretty forward except for when including expression characters and new lines
--within your specified search.
--Regular search/replace: type it out as it is shown in your sql worksheet.
--the code in worksheet
test11 where t5 = 'beta 9098';
--replace
test11 where t5 = 'beta 9098';
--Search Containing an expression: when searching for text that contain an expression, you will have to
--place brackets [] over the expression in order to be properly searched within Oracle SQL Developer.
--the code in worksheet
exec :connected_99 := :myCount +1
--replace
exec :connected_99 := :myCount +1
--Search Containing a carriage return: when searching for text that contain carriage returns, you will have to
--specify it as \n. \n does not need to be bracketed or even have a trailing space.
--the code in worksheet
select c1 from table44
where c1 = 'beta 9097';
--search
select c1 from table 44 \nwhere c1 = 'beta 9097';
----------------------------------------
----------------------------------------
----------------------------------------
--Set variable for connection counts
set serveroutput on;
var connected_count NUMBER;
exec :connected_count := 0;
exec :connected_count := :connected_count +1;
--select 'Connected to '||loc_name||'.' as name from location_inf;
exec :connected_count := :connected_count +1;
--begin
--DBMS_OUTPUT.PUT_LINE('Successful Connections:');
--DBMS_OUTPUT.PUT_LINE('Successful Connections: ' || TO_CHAR(connected_count));
--end;
print 'eh';
----------------------------------------
----------------------------------------
----------------------------------------
--Insert Based on Domain
--Example:
insert into haha
(reg, dice, min, max)
select (reg, dice, min, max)
from haha@sample.domainAddress.com where min= 44444 and dice = '888418 IT';
----------------------------------------
----------------------------------------
----------------------------------------
--Database Backups and Import through CMD--
--exp schema choice
expdp nanako/pass2@gemi directory=export dumpfile=mybackup.dmp logfile=restore2017.log schemas=nanako,myprod flashback_time=systimestamp reuse_dumpfiles=y
--exp with full backup
expdp nanako/pass2@gemi directory=export dumpfile=mybackup.dmp logfile=restore2017.log full=yes flashback_time=systimestamp reuse_dumpfiles=y
--imp table specification
impdp backup/back@GEMI_SERVER directory=export dumpfile=gemi_server.dmp logfile=new.log tables=mytable33,gemitable8
--imp exclude specification
impdp backup/back@gemi_server directory=export dumpfile=gemi_server.dmp logfile=new.log full=yes exclude=table:gemiTable33
--imp schema choice
impdp nanako/back@gemi_server directory=export dumpfile=gemi_server.dmp logfile=myRestore.log schemas=nanako
/*
-The database export location is defined within the dba_directories table.
-The backup and import commands used to be exp and imp. This went out of use with Oracle 11g. Now they are expdp and impdp.
-The backup can be specified within many ways. It can be a full backup, a backup specific to schema, specific to tables,
and have many other rules applied. You can even add an 'exclude' command to exclude a table.
Example: 'exclude=table:myTable33' or even 'exclude=schema:mySchema33';
-logfiles do not have to be preexisting. This process creates it.
-Always be careful what you are restoring.Specifying schemas or tables can help eliminate accidental issues.
-This will restore missing database objecs such as schemas and tables. This process will figure out tables exist
and skio them, and restore anything else that it finds missing.
-THis will not restore data that is missing from an existing table. Even though the code will sense to add a table if it
should belong there, it does not fill in the blanks with data. Consider droping and rebuilding the entire table, or set of
child tables. Always make sure that this is the best option.
-
*/
----------------------------------------
----------------------------------------
----------------------------------------
CREATE TABLE new_table as select <do the update "here"> from old_table;
----------------------------------------
----------------------------------------
----------------------------------------
--ORACLE: USING VARIABLES IN A LOOP TO DEFINE A QUERY
--------------------------------------------------------
--Example of loop that combines variables to form a full query. This loop is just here to show that this can
--be done. Will be produced 18 times.
set serveroutput on;
set VERIFY off
declare
n1 number := 0;
n2 number := 18;
a varchar2(50); --1st part of the select
b varchar2(8); --location defined
c varchar2(13); --2nd part of the select
d varchar2(50); --query output
combine1 varchar2(98); --full query
begin
while n1 < n2
loop
a := 'select location_name as name from location_info_table@'; --1st part of the select
b := 'GEMI';
c := '.myWebConnection.org'; --2nd part of the select
combine1 := a||b||c;
dbms_output.put_line(combine1);
execute immediate combine1 into d;
dbms_output.put_line(d);
n1 := n1 + 1;
end loop;
end;
--OUTPUT: 18 times
PL/SQL procedure successfully completed.
select location_name as name from loc_info_table@GEMI.myWebConnection.org
Cat Hospital of El Dorado
----------------------------------------
----------------------------------------
----------------------------------------
--LOAD DATA FROM FILE, AND TRUNCATE INTO TEMP TABLE
load data
infile 'c:\myfile.dat'
truncate
into table temp_error_table
trailing nullcols
(report_data position(01:2000))
;
----------------------------------------
----------------------------------------
----------------------------------------
--use of where clause in place of distinct
select
a.loc_id|| ' '||rpad(a.year|| '-'||a.type|| '-'||a.loc_rec_number,12)|| ' '||a.open_y_n|| ' '||
c.last|| ' '||b.name||b.age||b.ID
from loc a,
pet b,
owner c
where
a.id = b.location_id and
a.id = c.location_id and
b.id = c.pet_id and
a.type = 'H' and
b.check like '%223.48%'
group by a.loc_id,
a.year,
a.type,
a.loc_rec_number,
a.open_y_n,
a.last,
c.name,
b.age,
b.ID;
----------------------------------------
----------------------------------------
----------------------------------------
--Searching Up Objects Within SQL
--When tracking the processes for database objects or record types, it is beneficial to be able to search
through the database and find them.
--Within SQL Server, you had to query category type tables such as package tables specifically in order to look for a
--called database object. In most cases, 3rd party software such as redgate was incredibly helpful with finding all
--references in all SQL Server objects. After finding where the object is referenced, it is possible to begin building
--a map to track the object from start to finish, and see what it possibly evolves into or evolves from.
--Oracle, however, already has a table in which you can search objects. The user_source table lists object names, type,
--line, and the text associated. If I needed to find where the TABLE22.MY_FIRST_NAME column is referenced within other
--objects, I will use the following query to find it within any functions, procedures, packages, triggers, etc.
select * from user_source where TEXT like '%MY_FIRST_NAME%';
--This will select all of the objects that have the column name included. This is not limited to tables and columns, but you
--can search for called procs in other procs or packages, triggers, etc.
select * from user_source;
----------------------------------------
----------------------------------------
----------------------------------------
--SEARCH ALL REFRESH GROUPS
--Refresh groups are used within oracle. To query the refresh groups, you can use the dba_rgroup table for the main group,
--and the dba_rchild for child groups. The dba_rgroup includes all refresh groups.
select * from dba_rgroup where owner = 'LOC_44';
----------------------------------------
----------------------------------------
----------------------------------------
--TABLESPACES--
--Sometimes, a tablespace is needed in order to restore a database from a backup from a different location,
--or to a new server. If you attempt to restore the database or schema without the existance of the tablespaces
--that they are looking for, you will have to build them when the error indicates the missing tablespace names.
--To create a temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMPORARY_DATA
TEMPFILE 'temp_data.dbf' SIZE 5M AUTOEXTEND ON;
--To create a tablespace (quick method):
CREATE TABLESPACE MY_TABLESPACE_33
TEMPFILE 'my_tablespace_33.dbf' SIZE 5M AUTOEXTEND ON;
--to create a tablespace (setting more specifications):
CREATE TABLESPACE MY_DATA_33
DATAFILE
'my_data_33.dbf' SIZE 40M AUTOEXTEND ON NEXT 2M MAXSIZE 100M
EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT
AUTO;
----------------------------------------
----------------------------------------
----------------------------------------
--RESTORE FROM BACKUP - SPECIFY BY SCHEMA--
-You can bakup an entire database, and use an import with a specified schema or schemas in order to
--restore only the schemas you want from the full backup.In order to import a schema, you must log
--into an existing valid schema within the impdp command first.
-Imports and exports are run in cmd, not in sql plus or sql developer.
impdp gemi/pass33@GEMI directory=export dumpfile=latest_good_backup_schema_myLittleSchema.DMP logfile=myRestore.log schemas=cat_schema
--not specifying a schema
impdp gemi/pass33@GEMI directory=export dumpfile=good_dump_for_jury_mthomas170711.DMP logfile=myRestore.log
--extra exaple
impdp schema/password@database directory=export dumpfile=full_dump_file.DMP logfile=myRestore.log schemas=specific_schema,specific_schema2
----------------------------------------
----------------------------------------
----------------------------------------
