oracle add user


Waktu bermaen-maen disela-sela ‘kengangguran’, aku maen-maen oracle -karena aku ndak ngerti blass opo iku Pek-.
Lha….
Maunya sih bikin user baru di server oracle ku gitu, tapi ndak ngerti caranya.
Akhirnya, setelah minta bantuan Mas ABbas dikasih tahu carae bikin user, tapi sayang nya bikinnnya di console mode….Upppsss..

1. Pertama buka cmd dari RUN
2. Ketikkan sqlplus username/password oraclenya
3. Nanti ada tulisan : “SQL*Plus: Release 10.2.0.1.0…blablablabla”
4. Command prompt yang tadinya C:\> akan berubah menjadi SQL>
5. Itu berarti kita udah terhubung dengan oracle-nya
6. Kalo mau bikin user tinggal ketik : create user nama_user identified by nama_user ;
7. Itu kan cuman bikin user mas…kalo mau nambahin accessnya kasih perintah : grant previlege_yang_diinginkan to nama_user dan selalu akhiri dengan titik koma (;)

Misal :
C:\>sqlplus system/oracle-ku
(connect ke server oracle yang udah kita buat dengan user system password oracle-ku)
SQL>create user fahru identified by fahru
(bikin user yang bernama fahru password fahru)
SQL>grant connect,resource to fahru
(memberikan hak akses connect dan resource ke user fahru)
SQL>connect fahru/fahru
(nge-test koneksi user yang kita buat tadi)
Kalo mau ngasih user administrator ke user fahru caranya :(sebelumnya login sebagai admin oraclenya yang udah lo buat) lalu
SQL>grant dba to fahru
(memberikan akses database administrator ke user fahru)

My Pren, lek salah benerno yo…..:p

Source:

http://rozyee.wordpress.com/2007/09/25/oracle-add-user/

ORA-04031 unable to allocate 4200 bytes of shared memory


Error Message:

OCIStmtExecute: ORA-04031: unable to allocate 4200 bytes of shared memory (“shared pool”,”unknown object”,”sga heap”,”state objects”)

Cause:

More shared memory is needed than was allocated in the shared pool.

Analysis:

Bug on Oracle 8.1.7.2

Solution:

metalink suggest to set the parameter _db_handles_cached=0

Action:

If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters “shared_pool_reserved_size” and “shared_pool_size”. If the large pool is out of memory, increase the INIT.ORA parameter “large_pool_size”.

Links:

http://www.orafaq.com/maillist/oracle-l/2005/03/03/0169.htm

ORACLE – Alter Table – Add Column


We have “alter table” syntax from Oracle to add data columns in-place in this form:

alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);

Here are some examples of Oracle “alter table” syntax to add data columns.

alter table
cust_table
add
cust_sex  varchar2(1) NOT NULL;

Her is an example of Oracle “alter table” syntax to add multiple data columns.

ALTER TABLE
cust_table
ADD
(
cust_sex             char(1) NOT NULL,
cust_credit_rating   number
);

source:

http://www.dba-oracle.com/t_alter_table_add_column_syntax_example.htm

Oracle: Alter Table


Oracle provides “alter table” syntax to modify data columns in-place in this form:

alter table
table_name
modify
column_name  datatype;

If you are brave you can use a single “alter table” syntax to modify multiple columns:

alter table
table_name
modify
(
column1_name  column1_datatype,
column2_name  column2_datatype,
column3_name  column3_datatype,
column4_name  column4_datatype
);

Here are some examples of Oracle “alter table” syntax to modify data columns and note that you can add constraints like NOT NULL:

ALTER TABLE
customer
MODIFY
(
cust_name varchar2(100) not null,
cust_hair_color  varchar2(20)
)
;

We can also use Oracle “alter table” syntax in dynamic PL/SQL to modify data columns

BEGIN
SQL_STRING := ‘ALTER TABLE ‘||:TABLE_NAME||’ MODIFY ‘||:COLUMN_NAME||’ VARCHAR2(100′;

. . .
END;