This document is intended to be a help for those new to Oracle. The primary intention with this little guide is to help people getting started on creating users and tablespaces in their newly installed Oracle database. It's not intended to be a faq on how to install the database, because there are a few of those out there already, for links see resource section.
Any use of this material is on own risk. There's no guarantee that any of the things described in this howto works. The experiences this document describes where made on an Oracle 18.104.22.168.0 Database server installed on a Debian GNU/Linux System.
When you start of on a newly installed system you will need to create a user that will be accessing the database. For security reasons you shouldn't be using the system user nor the system tablespace. In the next sections you will learn how to create users and tablespaces for them to use.
A tablespace is where Oracle stores the data you put into your tables.
Syntax: create tablespace <TABLESPACE> datafile '<TABLESPACEFILE>' size <SIZE> M [autoextend on next <SIZE> M [maxsize <SIZE> M]]
TABLESPACE is the name you want to call the tablespace you're about to create. It's wise to give it a name that says something about what you're going to use it for. TABLESPACEFILE is the file, with fullpath, on your filesystem that has to contain the database. Depending on your installation it will probably reside in ~oracle/oradata/ORCL/ depending on what you set your Oracle SID to when you installed. Name this file after what you call your tablespace and put a .dbf as suffix and to do it the Oracle way you would generally add a number to the filename to, especially if you know that you will have to make more datafiles later on. So that would make the filename look like tablespace01.dbf. SIZE is pretty much self explanatory, depending on wheter you put a K or M after it the size is either in KiloBytes or MegaBytes. The autoextend on next command adds extra size to the tablespace when the amount of data exceeds the current size of the tablespace. It adds more size to the tablespace according to it's SIZE value and keeps extending until it runs out of diskspace. To avoid this you would add the maxsize command, which defines the max size of your tablespace.
You want to create a tablespace that uses 20 MB of diskspace to start of with, if the limit is hit it will need to add another 10 MB of diskspace to the tablespace. To avoid it from using up all of your diskspace a max limit of 50 MB is made.
create tablespace file_data datafile '/home/oracle/oradata/ORCL/file_data01.dbf' size 20M autoextend on next 10M maxsize 50M
To be able to connect to an Oracle database you need a user. A user can have different rights depending on what the user should be able to do.
Syntax: create user <USER> identified bye <PASSWORD> default tablespace <TABLESPACE> temporary tablespace <TEMP>
USER is what you want to call the user you want to create, this username doesn't need to have anything to do with your regular users on your system. PASSWORD is the clear text password you want the user to be using to connect to the database. TABLESPACE is where the user puts his tables by default. TEMP is the temporary tablespace used by Oracle to store temporary tables, rollback, etc. Oracle per default has a temporary tablespace called "temp".
You want to create a user that will be using the tablespace in the preceding example.
create user file_user indentified by file_pass default tablespace file_data temporary tablespace temp
For a user to be able to do anything at all you need to grant different priviligies to the user. There are a lot of different priviligies a user can have, where the most important one probably is being allowed to access the database.
Syntax: grant create session to <USER>
USER is simply the user you wish to give priviligies to connect to the database.
You want to give a user the possibility to connect to the database.
grant create session to file_user
alter user <user> quota <size> on <tablespace><user> username to alter quota for
alter user file_user quota unlimited on file_data"3.4 Granting user access to create tables,views,indexes
grant create table to <user><user> username of user to give access to create tables
grant create table to file_user"
create user select_file_user identified by select_file_pass default tablespace file_data temporary tablespace temp; grant create session to select_file_user; grant select on <thetableyouwanthimtoselectfrom> to select_file_user;An easier thing would be to do a special script after all the tables in the database has been created an run this command as the table-owning user:
select 'grant select on ' table_name ' to select_file_user' from user_tables;This will "create" the grant statements you need to run to make the select_only user able to select on the tables.
set echo off; set heading off; set feedback off; set pagesize 0; spool grant.sql; select 'grant select on ' table_name ' to select_file_user' from user_tables; spool off; set feedback on; set heading on; set echo on; @grant.sql;Summary of the howto:
create tablespace file_data datafile '/home/oracle/oradata/ORCL/file_data01.dbf' size 20M autoextend on next 10M maxsize 50M; create user file_user identified by file_pass default tablespace file_data temporary tablespace temp; alter user file_user quota unlimited on temp; alter user file_user quota unlimited on file_data; grant create session to file_user; grant create table to file_user;--
export EDITOR=emacsFor using this functionality you simply have to type ed on the command line. After editing the sql query like you want you just save the file and exit the editor. To execute the sql query you just enter simply press r.
"timestamp date default sysdate null"will create a column which defaults to system time with second precission.
select sysdate from dual;dual table is a single column value table.
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select to_char(timestamp,'MMDDYYYY') from lastchecked;
select count (id) from answer where data like '%mail%';under create table:
boolean varchar(1) default 'N' null check (boolean in ('N','Y'))
select location from locations start with id = '$page' connect by prior parent = id order by id ascTo connect to the Oracle database from a remote computer you need to add the hostname for the remote computer in the tnsnames.ora file on your local computer
NAME=(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=fqdn)(PORT=1521)) (CONNECTDATA=(SID=ORCL)) )Then you connect with:
~oracle/bin/sqlplus user/pass@NAMEset timing on / off slår timing af query til/fra desc <- mysql describe funktion drop user [cascade] select * from mq_users where trunc(created) > to_date('23-FEB-00');
To create the effect of limit function in MySQL you need to use the automatically-on-the-fly created rownum column. You can select and have a where clause that limits the amount of rows you want. Like: 'select id,name from table where rownum >= 1 and rownum <= 10' would get the first 10 results from the database.