Oracle vs. SQL Server - Oracle equivalents for SQL Server DBAs

SQL Server	Oracle
SELECT GETDATE()	select sysdate from dual
SUBSTRING function does not take argument for starting position with negative value	SUBSTR can have negative starting position in Oracle
SELECT INTO Table1 FROM Table2	CREATE Table1 AS SELECT FROM Table2
master database	data dictionary
syslogins view	dba_users view
sys.objects view	dba_objects view
sys.tables	dba_tables view
tempdb	Temporary tablespace
IDENTITY field automatically incremented by 1 on INSERT	Creating a sequence using: CREATE SEQUENCE name_seq START WITH 1000 INCREMENT BY 1; and a trigger to activate the sequence on BEFORE INSERT: CREATE OR REPLACE TRIGGER name_bir BEFORE INSERT on TableName FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT name_seq.NEXTVAL INTO :new.id FROM dual; END;
ISNULL() ISNULL(Field1, 0) returns 0 if Field1 is null	NVL() NVL(Field1, 0) returns 0 if Field1 is null

How to modify a column data type in a table that is populated with data

1) Create a table with the same name _tem (example: Table1_tmp) with the correct structure

2) Load data from Table1 into Table1_tmp using INSERT INTO ... SELECT FROM

3) Drop Table1

4) Re-create Table1, using CREATE TABLE Table1 AS (SELECT * FROM Table1_tmp)

5) Drop Table1_tmp

ALL_USERS vs. DBA_USERS vs. USER_USERS

There are 3 different sets of views ALL_, DBA_, and USER_.

ALL_ views display all the information accessible to the current user, i.e. it can looks at all the shcemas the user has permissions to, DBA_ views display infor for the entire database and is intended only for admins. Then USER_ views display info from the schema of the current user.

Creating database users in Oracle

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege. Usually, a newly created user is granted a role similar to the predefined roll CONNECT (used in this example) that specifies the CREATE SESSION and other basic privileges required to access a database.

create user user1 identified by pwd1 DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;


GRANT CREATE SESSION TO user1;

In order to check whether the user has been created, run

select username from dba_users;

and to check if you have the correct name for the tablespace, check dba_tablespaces table:

select tablespace_name from dba_tablespaces;

Tablespaces

One of the most basic concepts in Oracle is a tablespace. Tablespace is sort of a logical container used to group physical objects or datafiles. Each database contains at least one tablespace.

Tablespace is a logical entity because it is not visible in the filesystem. It consists of at least one datafile, which is physically located on the filesystem.. Oracle database contains at least SYSTEM tablespace, that contains most of the information about the structure and contents of the database.

Each table or index that is stored in the database, belongs to a tablespace. The tablespace acts like a bridge between Oracle database and filesystem.

Usually Oracle database also contains TEMP tablespace, that is used for sorting data on disk in case you run out of memory assigned for sorting data when you query the database.
Also usually there is USERS tablespace for storing user objects.

Great news!

Just received some absolutely wonderful news! I just got an offer from my company to join the DBA team as an Oracle DBA I!
It's a cause for celebration - new and exciting opportunities, Oracle training, lots of new and exciting things and I can still stay at the company I love where I spent nearly 7 years.

Javascript: Class.method vs. Class.prototype.method vs. Class.apply.method

Class.method - a custom method added in this way only exists for that particular instance of an object. if you want a custom method or property to be added to all instances of the object, that's where prototype object comes in.

Class.prototype.method - prototype object can help you to quickly add a custom method to an object that is reflected on all instances of it.

Class.apply.method - allows to apply a method of one object to another.