RECYCLE BIN,FLASHBACK

After dropping tables,it is feared to lose datas but still recoverable.

Just like in operating systems,dropping tables means throwing recyclebin.

As it is displayed in picture,there you can see the tables dropped with its details.

Let's see something different.

I will somehow recover the datas from COPY_EMP table with its object name like here;

I have created a table named it turkey with two columns; city and plate_number respectively.I manipulated it with insert statement then dropped it.

The flashback is run on to recover table to its previous state. Good one !

Some Sql Queries 4 (MERGE)

  As seen above,it is possible to do more than one data manipulation language (insert,update,delete) statement with merge statement.

when starting with merge statement,manipulation is made on table written after merge statement.
It is easy. Useful. More than one columns are able to be changed or more datas are able to be added or some rows are able to be deleted.

Some Sql Queries 3 (ALTER,INSERT ALL)

---dropping column
alter table hamdi drop column birthdate;
---renaming table's name
alter table hamdi rename to hamdi2;
---renaming column's name
alter table hamdi2 rename column salary to income;


Here,we have insert all statement 

After 'into' statement we wrote some aliases inside 'values' parenthesis that was used in select statement.

In here aliases that were created work as if they are references filled in select sub-query.

Some Sql Queries 2 (INSERT,ALTER)

insert into (select location_id,city,country_id
            from locations )
values (3500,'New_York','US');


I can insert values as shown format above.Normally,this table has more columns than shown inside insert statement but i just wanted to insert specific columns. Rest of them are null at the row.




Here i altered table by adding a new column 'hamdi'. What a simple!

Some Sql Queries 1 (NOT EXISTS,WITH)

NOT EXISTS 

select department_name
from departments outer
where not exists (select '23523532'
from employees
where department_id=outer.department_id);


In here,this query is written regarding the HR Sample Schemas.

I just wanted to use 'not exists function' to understand how it works.

In here query shows the deparments names' which were not used in 'employees' tables.

'not exists' function finds the appropriate coloumn in 'employees' table itself whatever it is. In subquery there is number '23523532',it does not count anything. Here, anything can be written in here. As if the function itself finds whatever needed and sort out the expected result.


WITH ...

1-)
In this example, in memory we create a table which normally does not exist but in memory and we put its name dept_costs having two columns
department_name, dept_total (SUM(e.salary)) with a subquery joinging two different tables which exist in HR database(employees, departments)

2-)
At this part, we create another table with a column named 'dept_avg' in memory from previous table's columns which was also created in memory.

3-)
In this part we sort out a result with these two tables created in memory.

REP-0110 Unable to open file '.....'

There might be more than one reason that causes this issues;

1-)Incorrect REPORT_PATH in registries
2-)Unsufficient privileges to acccess the files' path.

In my case,the user who had this problem could not access the file path due to some OS concerns.
The password user uses expired so needed to be changed to access specified path of file called by report.

HOW TO CHECK CURRENT USER'S PRIVILEGES

Perhaps,you want to create a user whom priveleges will be the same as someone's privileges but you do not know this someone's privileges on database.Here's an helpfull code which will show you the way.

set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',user)
from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)
from dual;


Just after writing the code above,press F5 button to see magic.

ORA-20000 Oracle text error DRG-10599 column is indexed

I was trying to compare LIKE  operator and CONTAINS function inside a query to analyse their speed to retrieve data.

I used HR Sample Schema table EMPLOYEE.

What i had to do is to create an index on the column which i tried to retrieve data from.

Then the problem was solved.

But the speed differed at each time that i compiled the queries one to another.I could not come up with an overall idea about using CONTAINS function.

I did the same operation with a table which has million datas in it.

Contest won by CONTAINS function by multiplying five times earlier the operator LIKE.



Index is significant. Speed is important. Experience of database you are using considerably makes difference.

<----LEFT JOIN ----------------------- RIGHT JOIN-----> VARIATIONS

I simply used HR tables DEPARTMENTS AND EMPLOYEES

What does left join do?

Simple.

It takes all datas from the left table  even not matching with the datas joined table's joined datas..

What is from the left table? The table at the left side when joining operation is operated.

LEFT JOIN

RIGHT JOIN

ANOTHER VARIATIONS

LEFT JOIN

RIGHT JOIN

LEFT JOIN

RIGHT JOIN

Oracle SQL Developer Unable to Find Java Virtual Machine

When i try to open sqldeveloper interface,it has shown me a prompt to browse java jdk path. I had java jdk installed on my computer which was 64 bit. I browsed the 64 bit java jdk path but it displayed me an error as in picture.

Notice that
sqldeveloper does not accept 64 bit installed java jdk.It has to be 32 bit.

STEPS THAT I HAVE TAKEN
1-)Downloaded jdk_1.6.0_45
2-)Set Java Home in sqldeveloper.conf file path of jdk_1.6.0_45 as shown below.

There you go!

What is the difference between varchar2 and char?

We have  same datatype which is string but can be defined with different names.
These two names are defined whilst writing in pl-sql codes in format just like i showed down here;

varchar2(size)
char(size)

They might contain same string value for instance my name 'HAMDI' but why their definitions are different.

Let's have a look this issue with an example.
Let's imagine i declared them as they are below;

my_name varchar2(30);

my_name char(30);


i assigned them 'HAMDI'

string value 'HAMDI' has 5 characters. H-A-M-D-I

when char with my_name definition hold this data,it creates 30 character space in storage even it has 5 characters in it and it keeps rest 25 characters space still empty on storage.
In short,it tells storage that whatever i have as a value inside me, give me an area or space for 30 characters.


when varchar2 with my_name definition hold this data,it just request until 30 character space but takes a space for 5 characters in storage.
Shortly, I have a limit until 30 characters but i have 5 characters now so i am taking a space for 5 characters now.



if you are seeking for performance,char is better than varchar2

for example,if you want to update and make longer the size of content data,it will easily use fixed-space  for character specified under char but if you do the same operation with varchar2,it will try to find a space in storage.

LOCK,UNLOCK HR SAMPLE SCHEMAS

I will show how to lock and unlock HR sample schemas from different ways

1-)Command Prompt;Locking,Unlocking HR

2-)Sql Developer (Locking HR)

3-)Error after trying to log in with locked HR 

 4-)Unlocking HR

Database Configuration Assistant

Here I have database configuration step by step;

When i configure a database, i used instrunctions written on a book which is written in Turkish Language. I want to advertise this book at end of all pictures because it is worth to do it. It is a good one for beginners. Simple,Readable,Understandable                   

When the subject comes to set up and configurations issues,it is significant. You need to do each step correctly and you need to be aware of what each step means.

                                                 

1-)Click on the next button

2-)Click on the next button

3-)Here we are choosing what kind of reason we are creating this database for.The choosen option is for Online Transaction Processing.

4-)We write the global name of database,we can write also SID(System Identifier) name with global name but  in RAC (Real Application Clusters) it has to be given differently. But with single instance configuration these names might be same.

5-)We set Oracle Enterprise Manager console in here. OEM(Oracle Enterprise Manager) can be load and configured later too. We do not do any action on Automatic Maintenance Task.

6-)Here we have the most administrative users. Instead writing password for each user,we defined same password for all of them.

7-)Here we might have an error telling us that password does not match Oracle recommended password complexity policy. You can change it but clickin on no will take you another step.

 8-)Here we are choosing Storage Type.This is also asked while deinstalling database.I created a folder named data_DBHAMDI under my ORACLE_HOME path.

9-)Due to instructions on book i did not clicked on these two options which will be explained next book.

10-)I did something reverse than the book i followed at this step. I used Sample Schemas and i clicked on this option. Because i need HR tables which come with sample schema tables.It will consume 

11-) Automatic Memory Management was selected and we let oracle to manage it.We could also specify PGA and  SGA. We can do all later.

12-)We chose character set of database.It is 'WE8ISO8859P9'

13-)It can be seen where data files,control files and online redo log files are stored at picture shown below and the locations are possible to be changed.As defaults,two copy control files are created.It is likely to create more than one control file. It is also likely to be able to change the sizes of redlog files and add new ones.It is also possible to do same instructions after database configuration.

14-)Click on Create Database and Finish

15-)Click on OK.

16-)After completing configuration,another screen will be shown which tells database configuration details.

It is possible to unlock users and password change from Password Management.Enterprise Manager links are also given (Database Control URL)

Buy it! It will be helpful!

By the way ; I do not know who the author is personally. (Footnote)

LISTENER CONFIGURATION

To able to create a database and to able to use it in your localhost you need to create firstly listener which comes with default values. This is step by step configuration of a listener. It is not possible to have a database either local or not without listener. What is listener? We will examine this question later on but here we have how to configure it simply

1-)

2-)

3-) you can give any name in here to a listener that you are about to create.I used the default one.

 4-)

5-)

6-)

7-)

8-)

Error while trying to retrieve text for error ORA-01019

My manager wanted me to check out this error which occured at a remote computer trying to connect our oracle database.
After a google operation and i came to a quick result.

There were more than one ORACLE_HOME path inside system variables.
So to overcome it. Check out the paths that are in system variables.

In my case there were more than one path as shown below in an order;
C:\oracle\BIToolsHome_1;C:\app\HAMDI\product\11.2.0\dbhome_1\bin;C:\oracle\BIToolsHome_1

So i remove the paths written repeatedly. Then we were able to make it connecting to our database.

The source that guided me to solve this issue;

http://stackoverflow.com/questions/12560925/error-while-trying-to-retrieve-text-for-error-ora-01019

DIM - 00019 : Create Service Error O/S Error : (OS 1703) ORACLE DATABASE

When i try to create a database, i had this error.
The reason that causes this error is simple.

I had created once a database with HAMDI name and after removing database i did not remove all regedit registries completely and there were some services running related to this database name. So whilts creating a new database with same name,it inevitably had shown me an error.

So be careful about removing database completely without even missing any file running with system.

ORA-00911 invalid character (Database Configuration Assistant)

I had an error as written at title while creating a database from Database Configuration Asisstant at last step when it is being created.

I tried changing the Database Name and SID and data file name that i created for my database.

My database name was DBHAMDI and i changed it to HAMDI

My database's data folder's name which i created under my ORACLE_HOME was 'data_DBHAMDI'

and i used lower case to guarentee it. 

I did not get why i had this error because there was not any wrong definition like using Non-English Character or something like this but googling showed me some sort of way like i did and It worked.

No more error :)

'sqlplus' is not recognized as an internal or external command, operable program or batch file. (COMMAND PROMPT)

I had started to use my home-Laptop as a lab pc which i use seperately than one i use in my company.
The reason is to experiment oracle cases to experience the warnings,responds,results and to see how to overcome them.Shortly my pc is my cavy in my lab;)

If you had the same problem,the solution is simple. Just add your ORACLE_HOME path to your
Environment Variables. For instance;
To find ORACLE_HOME path. Look it up from registry;

Windows --->Run--->regedit--->HKEY_LOCAL_MACHINE
CTRL + F 
type ------>ORACLE_HOME 
Press----->Find

C:\oracle\BIToolsHome_1; 

Right Click Computer--> Advanced System Settings-->Advanced

                                          
Click Edit----- Add your ORACLE_HOME path to the very begining of line. Do not forget to put semi colon to separate it from other path and then click OK.