This forum is currently locked. You can't register or post questions at this time. (read more)

My readings

All posts relating to Oracle database administration.

Moderator: Tim...

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

My readings

Postby anilvvkumar » Mon Sep 09, 2013 2:04 pm

Respected sir,

I have purchased book from market, Expert oracle database administration by Sam R Alapati. and reading Normalization of database. I understood upto 3rd Normalization, but I didn't understood 4th and 5th normalization.

Secondly I read RAID concepts also, I understood RAID 0, RAID 1, RAID 5, but I did n't understood what is the difference between 2,3,4 RAID levels.

Thanks & Regards,

V. Anil Kumar.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Mon Sep 09, 2013 3:16 pm

Hi.

1) You are unlikely to use anything beyond 3rd normal form in a real database, so I wouldn't worry about it. :) I would have to go and look up the definitions myself if I had to try and explain it to someone. :)

2) I have a brief post about RAID here.

http://www.oracle-base.com/articles/mis ... d-raid.php

Have a read and see if that answers your question. If not, come back to me. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Mon Sep 09, 2013 6:30 pm

Respected sir,

Thanks for reply..

As day to do day I am reading new things, what about my old reading, How can I remember those readings.

Shell I spare some time to refresh my old knowledge and practices, so I can practice old things as well as I can read new things ? How to manager old & new ?

Thanks & Regards,

V. Anil Kumar.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Mon Sep 09, 2013 6:33 pm

Hi.

The reality is you only use a small proportion of your skills in your day-to-day job. You either have to keep revisiting old stuff, or forget it. The choice is yours.

My suggestion is you make a point to remember general principles, rather than specifics. I screw up syntax on a daily basis. Without my website to copy/paste from I would be screwed. The important thing is you understand how to approach problems and understand the concepts of the technology you are using. Spending ages trying to remember the syntax for specific commands is irrelevant.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Mon Sep 09, 2013 9:10 pm

Sir,

I didn't understand your words "Without my website to copy/paste from I would be screwed" is that means you are not doing copy / paste of commands, which actually means you remembering the syntax of commands is it?

Regards,

Anil Kumar

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Mon Sep 09, 2013 9:49 pm

Hi.

No. It means totally the opposite. I am totally reliant on my website. I use it to copy&paste examples from all the time as I have a terrible memory for syntax.

The important point is I remember principles and I know what can and can't be done with different bits of functionality. Spending time revising syntax is a waste. That is what the manuals are for. Remember principles is very important. What's more, they often translate between other bits of technology...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Tue Sep 10, 2013 1:08 pm

Sir,

Select count(*) from user_tables;

above quiery gives count of user_tables,

I have a doubt that how to get table name with row count ?

I think you know in foxpro if we give dir command in command window, it will display table name with number of rows.

Is it possible to get that list in Oracle?

V. Anil Kumar

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Tue Sep 10, 2013 2:35 pm

Hi.

If you are happy to get the number of rows from the table when the last stats were gathered, you can do this.

Code: Select all

SELECT table_name, num_rows
FROM   user_tables;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Tue Sep 10, 2013 4:08 pm

Sir,

Thanks for reply..

Really I feel ashmed becuase, I didn't see the structure of user_tables description and asked you that question.

Really I am sorry.

V. Anil Kumar.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Tue Sep 10, 2013 4:11 pm

LOL.

Not worries. But remember, the value is based on the last time the stats were gathered, so it can be a bit out of date. The last_analyzed column tells you when the stats were last gathered for each table.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Wed Sep 11, 2013 2:19 pm

Respected sir,

Thanks for reply..

But I think stats were updated with commit transcation is doen. Is it?

Thanks & Regards,

Anil Kumar

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Wed Sep 11, 2013 2:41 pm

Hi.

No. Table stats are only updated when stats are gathered for the table. The nightly stats run only gathers stats for stale tables, so the numbers could be out of date by about 10% before you are definitely going to need stats gathered.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Wed Sep 11, 2013 4:02 pm

Sir,

OK Fine..

Is there any query to get table name and respective updated row count. Generall we will get row count by giving following sql..

Select count(*) from table_name_xyz;

Can we do any join above count with select table_name from user_tables query...

Thanks & Regards

V. Anil Kumar.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: My readings

Postby Tim... » Wed Sep 11, 2013 4:35 pm

Hi.

Not really, because you don;t know the tables in advance, so you can't write a regular query to do it. You can use PL/SQL and dynamic SQL.

Code: Select all

CREATE OR REPLACE FUNCTION table_count (p_table_name IN VARCHAR2)
  RETURN NUMBER
AS
  l_table_name VARCHAR2(30);
  l_number     NUMBER;
BEGIN
  l_table_name := SYS.DBMS_ASSERT.sql_object_name(p_table_name);
 
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :l_number FROM ' || l_table_name
    INTO l_number;
  RETURN l_number;
END;
/


Code: Select all

SELECT table_name, table_count(table_name) AS count
FROM   user_tables;

TABLE_NAME                          COUNT
------------------------------ ----------
T1                                      0
T2                                      1

2 rows selected.

test@db11g>


Remember, this will be a massive performance hit on the database if you have lots of big tables, as each table will need a full table scan. Personally, if someone asked me to do this, I would suggest they didn't!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

anilvvkumar
Member
Posts: 41
Joined: Thu Aug 29, 2013 9:16 pm

Re: My readings

Postby anilvvkumar » Wed Sep 11, 2013 6:41 pm

Respected Sir,

Thanks for reply..

OK I got it, is there any manuall proceedure / command to update stats of user_tables.

And secondly, I have discussed with a collegue related to Logical database structure. He asked me each database is having different table space or one table space can we assign to different databases ? I know a table space contain different datafiles.. that means a table space can expand to multiple physical datafiles. I have a doubt that a table space is vary database to database..

like wise we have a doubt related to physical data file also, each database is having seperate datafile or one datafile can contain multiple databases ? I mean database means oracle SID. If we assign same datafile to different databases (SID's) while creation of database manually.. System will accept ? I mean physical data file is significant from database to database.

Thanks & Regards,

V. Anil Kumar.


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron