How to modify a parameter in SPFILE

Method 1:

1. Run
   

   ALTER SYSTEM SET processes=(processes + 5) SCOPE=SPFILE SID='*';
   

2. Reboot (Issue shutdown and startup)

if it is not a parameter that can be modified via ALTER SYSTEM then use:

Method 2:

1. Run
   

   CREATE PFILE='$ORACLE_HOME/dbs/initSID.ora' FROM SPFILE='location/spfile.ora';
   

   this will create PFILE called initSID.ora at $ORACLE_HOME/dbs
2. Make changes to initSID.ora file via vi editor



3. Shutdown the database



4. Startup (nomount) the instance where you created and altered the pfile using this pfile only




STARTUP PFILE=$ORACLE_HOME/dbs/initSID.ora

5. Create a new spfile




 CREATE SPFILE='location/spfile.ora' FROM PFILE=‘$ORACLE_HOME/dbs/initSID.ora’;

6. Shutdown this instance again



7. Now startup normally without PFILE or SPFILE option:




STARTUP

8. Remove the PFILE (Optional)



9. To confirm that the parameter has been modified, issue following sql statement:




SHOW PARAMETER 

[INS-30043] The grid infrastructure home does not exist or is empty

I have gotten this error while attempting to re-install Oracle on the UNIX server.

The old version of Oracle was not de-installed properly, and so when new version was being installed, I got an error "[INS-30043] The grid infrastructure home '/u10/app/oracle/product/11.2.0/grid' does not exist or is empty".sion i was installing was 11.2.0.3 and the folder 11.2.0 didn't even exist.

The resolution was quite simple though - manually editing inventory.xml file. It can be found in $ORACLE_BASE/oraInventory/ContentsXML directory. I had to remove the entries pointing to the deleted folders and my issue was resolved.

inventory.xml file lists all the Oracle products installed on the machine and when directories are deleted manually, the entries are not removed from inventory.xml file. That's why it is a good idea to perform a deinstallation when removing any Oracle products from the server as opposed to just manually removing directores (applies to 11.2.0 and higher)

Some useful commands for vi Editor

Command	Action
x	delete one character
dw	delete current word
dd	delete current line
D	delete all content to the rigght of the cursor
:u	undo last command
:q	quit editor without saving
:wq	save and quit editor
:w	write without exit
:[n]	goto line [n]
b	move backwards one word
i	begin inserting text at the current cursor location

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.