Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Unanswered: dba priveleged commands - Is there an alternative?

    Hello everyone!

    I have a question regarding the use of dba priveleged commands.

    There are commands like 'truncate', 'bcp', etc., which can be executed only by the dba or someone who has dba priveleges.

    Suppose I have a requirement to delete all rows in a table, I can either
    1) use delete * from table
    or
    2) use truncate table

    The delete option does not necessarily require a dba privelege for execution whereas a truncate does.

    If I use delete option then the temporary database fills up. To prevent this I need to set 'trunc log on chkpt' option to true for which I need to have dba privelege. Hence for both the cases (truncate and delete with truncate log) the dba privelege is required.

    Is there any option to delete all rows in a table without filling up the transaction log using a user id which does not have dba priveleged permissions?

    Similarly, is there an option to use bcp using a user id which does not have dba priveleged permissions?

    If there is an option,
    1)What are the performance issues?
    2) What can be done to bring up performance level?
    3) What are the risk factors?

    Writeback your valuable answers.

    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    140

    Re: dba priveleged commands - Is there an alternative?

    hi

    as far as tempdb filling problem is there it doesnt matter which id you use so better you increase tempdb


    regarding your question about a user need to delete row from the table
    you can explicitly grant delete permission to the user using GRANT command on a particular table

    also as far as a user has select and insert rights on the table
    that user can bcp in the data on that table and the user doesnt require dbo previlages you can also grant select and insert permission on the table


    hope this helps



    iQUOTE]Originally posted by blues
    Hello everyone!

    I have a question regarding the use of dba priveleged commands.

    There are commands like 'truncate', 'bcp', etc., which can be executed only by the dba or someone who has dba priveleges.

    Suppose I have a requirement to delete all rows in a table, I can either
    1) use delete * from table
    or
    2) use truncate table

    The delete option does not necessarily require a dba privelege for execution whereas a truncate does.

    If I use delete option then the temporary database fills up. To prevent this I need to set 'trunc log on chkpt' option to true for which I need to have dba privelege. Hence for both the cases (truncate and delete with truncate log) the dba privelege is required.

    Is there any option to delete all rows in a table without filling up the transaction log using a user id which does not have dba priveleged permissions?

    Similarly, is there an option to use bcp using a user id which does not have dba priveleged permissions?

    If there is an option,
    1)What are the performance issues?
    2) What can be done to bring up performance level?
    3) What are the risk factors?

    Writeback your valuable answers.

    Thanks
    [/QUOTE]

  3. #3
    Join Date
    Oct 2003
    Location
    usa
    Posts
    8
    <<
    Is there any option to delete all rows in a table without filling up the transaction log using a user id which does not have dba priveleged permissions?
    >>


    I see there are two problems :
    1. able to truncate.
    2. transaction log fills up.


    Only the DBO (database owner) can truncate a table. The reason being a "truncate table" command is a non-logged operation and since it puts the recoverability of database in question, this is a privileged command. There is no way around it.

    Your second problem is that transaction log fills up. This can be overcome by either splitting your transaction into smaller manageable pieces or increasing size of transaction log. Also, if you are BCP'ing into a table with indexes, dropping those indexes before BCP and setting "TRUNCATE LOG ON CHKPT" will drastically reduce the amount of transaction log space required.

    Hope this helps.
    abhay kumar

  4. #4
    Join Date
    Nov 2003
    Posts
    4
    Originally posted by lucknowm
    <<

    Your second problem is that transaction log fills up. This can be overcome by either splitting your transaction into smaller manageable pieces or increasing size of transaction log. Also, if you are BCP'ing into a table with indexes, dropping those indexes before BCP and setting "TRUNCATE LOG ON CHKPT" will drastically reduce the amount of transaction log space required.

    Hope this helps.
    abhay kumar
    Thank you Perl and Abhay for your answers.

    Don't we require dba privelege to set 'truncate log on chkpt' option to true or false?
    Don't we require dba privelege to drop or create or restore index on a table?

    Without using the dba user id, is it possible to bcp, delete data without performance degradation?

  5. #5
    Join Date
    Oct 2003
    Location
    usa
    Posts
    8
    << Don't we require dba privelege to set 'truncate log on chkpt' option to true or false? >>

    Yes, you do.

    << Don't we require dba privelege to drop or create or restore index on a table? >>
    Not sure what you meant by restoring index on a table. You should either be the DBA or table owner to create and drop index on a table.


    << Without using the dba user id, is it possible to bcp, delete data without performance degradation? >>

    Yes there is. In fact I BCP IN large amount of data without using DBA privileges. Please send me the following :

    - DDL of your table ( including indexes )
    - Version of Sybase ( use following command : select @@version )
    - Amount of Data you want to BCP IN

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Originally posted by lucknowm
    Yes there is. In fact I BCP IN large amount of data without using DBA privileges. Please send me the following :
    BCP out only requires SELECT permission on the table
    BCP IN only requires INSERT permission on the table

    Only fast bcp requires "SELECT INTO" option in the database
    Only fast bcp doesn't log the inserted rows.

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    Thanks Abhay and fadace for your response.


    -----------------------------------------------------------------------
    Only fast bcp requires "SELECT INTO" option in the database
    Only fast bcp doesn't log the inserted rows.
    ------------------------------------------------------------------------

    Do we need dba privelege to enable the "SELECT INTO" option in the database for fast bcp?

    Will it be possible to fast bcp in a table for an user id with INSERT permission on the table?

    If slow bcp is used in a table with INSERT permission for an user id, what will be the performance issues? Suppose if there are around 30,000 to 40,000 rows to be copied into the table how will it differ in terms of performance when fast bcp is used and when slow bcp is used.

    Do you have ways on increasing performance when slow bcp is used?

    Thanks

  8. #8
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Do we need dba privelege to enable the "SELECT INTO" option in the database for fast bcp?

    => DB owner or sa_role

    Will it be possible to fast bcp in a table for an user id with INSERT permission on the table?

    => Yes

    If slow bcp is used in a table with INSERT permission for an user id, what will be the performance issues? Suppose if there are around 30,000 to 40,000 rows to be copied into the table how will it differ in terms of performance when fast bcp is used and when slow bcp is used.

    => risk to fill-up the log
    => use bcp -b1000, or bcp -F n -L m to pass the data step-by -step

    Do you have ways on increasing performance when slow bcp is used?

    => Transform slow to fast dropping the index, and recreating them later
    => minimize the # of indexes
    => use partitionned tables and partitionned bcp commands
    => improve performance using APF, named caches, bigger buffer pools, bigger network packet size (bcp -A n), etc...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •