• Personal tools
  • Log in / create account
• Navigation
  • Home
  • Wiki Home
  • Consulting
  • Hosting
  • web contact form
  • Info
  • Recent changes
  • Random page
  • Help
• SEARCH
• TOOLBOX
• LANGUAGES
• Toolbox
  • What links here
  • Related changes
  • Special pages

Oracle statements

From YourSupportStoreWiki

now Lets do the basic oracle sql statements.

this is the most useful in my opinion

it pipes the output to a file called filename.txt equivalent to the unload command in informix.

SPOOL filename.txt
Select * from table;
SPOOL OFF

First lets find all of your databases.

SQL>select name from v$database;

Now lets see all of the users.

SQL>select username from dba_users

Lets look at your tablespaces and list them all.

SQL>select tablespace_name from dba_tablespaces

Now lets look at your tables

SQL>select table_name from dba_tables;

now if you want to see whats in the tables

SQL>describe tablename;

If you want to see all of the tables in a tablespace

SQL>select table_name from dba_tables where TABLESPACE_NAME = 'tablespacename'

and if you know the owner of the table.

SQL>select table_name from dba_tables where owner ='JEREMI'

and if your not sysdba and you want to see all of your tables and views.

SQL>show tab;

and if you are logged in as a user and you just want to see your own tables but not views.

SQL>select OBJECT_NAME from user_objects where object_type = 'TABLE';

deleting table information

delete and comit;

SQL>truncate table tablename;

or

SQL>delete from tablename; commit;

destroying all table data including removing all column and constraint information.

SQL>drop table tablename;

now create a user

CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <tablespace_name> TEMPORARY TABLESPACE <temp_tablespace_name> QUOTA <quota_amount> ON <tablespace_name> PROFILE <profile_name> PASSWORD EXPIRE;

and then create a table

create table username.tablename (

column1 number(6),
column varchar2(30),
column number(6),
column number(6)
)
tablespace tablespacename;

now lets create an index.

creating a b-tree index is default

CREATE INDEX index_name ON table_name(column_name)
TABLESPACE tablespace_name;

now create a bitmap index

 CREATE [bitmap]INDEX index_name ON table_name(column_name)
 TABLESPACE tablespace_name reverse;

now create a reverse bitmap index.

 CREATE [bitmap]INDEX index_name ON table_name(column_name)
 TABLESPACE tablespace_name reverse;

her are some random statements that can be useful. Be carefull

select * from dba_data_files

Select account from table where account=value

Where account and value are bin variables.

v$database v$log

column <name> format a2000 word_wrap

select kqftanam from x$kqfta;